[MySQL] Are These Possible?

aexi0n · **Author**

I've been trying to figure out a query that is capable of grouping by column X, and column Y, while using the aggregate function MIN() on column Z. Then I would like to Select all the rows from the results that match a given steam id.

To put this in perspective, imagine this is what my table looks like this:

Code:

        _________________________________________________
	|Name	|SteamID|Map	|Style	|Record	|Date	|
	|_______|_______|_______|_______|_______|_______|
	|Bob	|1293	|Map A	|Auto	|21.0	|3/11	|
	|_______|_______|_______|_______|_______|_______|
	|Mark	|2814	|Map A	|Auto	|29.0	|3/12	|
	|_______|_______|_______|_______|_______|_______|
	|Ella	|3999	|Map A	|Auto	|28.0	|3/13	|
	|_______|_______|_______|_______|_______|_______|
	|Ella	|3999	|Map B	|Auto	|25.0	|3/13	|
	|_______|_______|_______|_______|_______|_______|
	|Bob	|1293	|Map A	|Legit	|19.0	|3/13	|
	|_______|_______|_______|_______|_______|_______|
	|Bob	|1293	|Map B	|Auto	|20.0	|3/14	|
	|_______|_______|_______|_______|_______|_______|
	|Mark	|2814	|Map B	|Legit	|22.0	|3/14	|
	|_______|_______|_______|_______|_______|_______|

Now I would like to use the GROUP BY clause, i.e: GROUP BY 'table'.'map', 'table'.'style', and also use the MIN() aggregate function like so: MIN(record). Ideally the SQL Query would look something like:

Code:

SELECT * FROM (SELECT `map`,`steamid`,'style', MIN('record') FROM `records` WHERE record > 0 GROUP BY `records`.`map`, `records`.`style`) AS temp WHERE 'steamid' = '%s';", steamid

Thus I would get an end result similar to the table below.

Code:

	_________________________________
	|SteamID|Map	|Record	|Style  |
	|_______|_______|_______|_______|
	|1293	|Map A	|21.0	|Auto   |
	|_______|_______|_______|_______|
	|1293	|Map A	|19.0	|Legit  |
	|_______|_______|_______|_______|
	|1293	|Map B	|20.0	|Auto   |
	|_______|_______|_______|_______|

However, this is not the case. Using the above query I receive no results, and cannot figure out why.
There are no errors, so the query is being executed properly, and the database should return results.
Does Sourcemod MySQL support using GROUP BY/MIN()? Is there something wrong with my query?

Impact123 · *Last edited by Impact123; 03-11-2015 at 15:35.*

Besides your queries don't make much sense to me, the improper use of ' seems like to be the problem here.
You should get your hands on a proper desktop mysql client like heidisql und test your queries.

Exolent[jNr] · 03-11-2015 , 15:31 Re: [MySQL] Are These Possible?

Code:

WHERE 'steamid' = '%s'

You are putting the column name in quotes rather than ` character. That is the issue.

EDIT:

And also here, too:

Code:

MIN('record')

aexi0n · 03-11-2015 , 21:07 Re: [MySQL] Are These Possible?

Ah. I'm an idiot. That was the problem >.> Thank you very much.