asked on In MySQL, how do I select a set of records from the most recent date?
I'm trying to select the data from the most recent date/year in a table. For example, I have the following data:
I want to get the result:
From what I've read, I got the impression the following query would work:
But that returns the first set of percentages (from 2015) with the most recent date.
What am I doing wrong and what should I be doing instead? Thanks!
countyID | datadate | dataPercentage
1 | 2015 | 54
2 | 2015 | 60
3 | 2015 | 51
1 | 2016 | 54
2 | 2016 | 59
3 | 2016 | 53
1 | 2017 | 56
2 | 2017 | 64
3 | 2017 | 59
1 | 2018 | 56
2 | 2018 | 72
3 | 2018 | 57
I want to get the result:
1 | 2018 | 56
2 | 2018 | 72
3 | 2018 | 57
From what I've read, I got the impression the following query would work:
SELECT countyID, dataPercentage, MAX(dataDate)
FROM myTable
GROUP BY countyID
But that returns the first set of percentages (from 2015) with the most recent date.
1 | 2018 | 54
2 | 2018 | 60
3 | 2018 | 51
What am I doing wrong and what should I be doing instead? Thanks!
MySQL Server
Last Comment
SiobhanElara
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
By the way...
The query you tried initially (that didn't do what you wanted) uses a MySQL "weakness" that you should avoid. In standards complain SQL system you MUST include ALL "non-aggregating" columns of a select clause into the group by clause. Note that your query does NOT do this:
SELECT countyID, dataPercentage, MAX(dataDate)
FROM myTable
GROUP BY countyID
Under certain conditions (when ONLY_FULL_GROUP_BY is off) MySQL allows a user to bypass this standard syntax making it look oh so clever and oh so easy. Ha! No it isn't.
What MySQL actually does in a query like yours is APPROXIMATE what the values of dataPercentage will be displayed. Queries like this are NOT RELIABLE and SHOULD BE AVOIDED. Please read MySQL Handling of GROUP BY
The query you tried initially (that didn't do what you wanted) uses a MySQL "weakness" that you should avoid. In standards complain SQL system you MUST include ALL "non-aggregating" columns of a select clause into the group by clause. Note that your query does NOT do this:
SELECT countyID, dataPercentage, MAX(dataDate)
FROM myTable
GROUP BY countyID
Under certain conditions (when ONLY_FULL_GROUP_BY is off) MySQL allows a user to bypass this standard syntax making it look oh so clever and oh so easy. Ha! No it isn't.
What MySQL actually does in a query like yours is APPROXIMATE what the values of dataPercentage will be displayed. Queries like this are NOT RELIABLE and SHOULD BE AVOIDED. Please read MySQL Handling of GROUP BY
If ONLY_FULL_GROUP_BY is disabled, a MySQL extension to the standard SQL use of GROUP BY permits the select list, HAVING condition, or ORDER BY list to refer to nonaggregated columns ... [then] the server is free to choose any value from each group, ... which is probably not what you want.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thank you both! Unfortunately I'm still on MySQL5, but am looking forward to those window functions.
Open in new window