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?