MySQL query to select only last rows of groups of dates

Question

I have the following query:

SELECT elo, date(date_calculated) date FROM users_historical_elo WHERE uid =36 order by id asc

that produces this result:

| elo | date

|984|2011-04-04

|1010|2011-04-04

|1036|2011-04-04

|1016|2011-04-08

|1000|2011-04-08

|944|2011-04-09

|973|2011-04-09

...

I need help writing a query that selects only the last "elo" grouping by date. So the output should be:

| elo | date

|1036|2011-04-04

|1000|2011-04-08

|973|2011-04-09

...

Thanks!

http://stackoverflow.com/questions/3449757/sql-group-by-records-and-then-get-last-record-from-each-group — Naveed, May 29 '11 at 01:09
@NAVEED, the code I accepted as answer here is more than twice as fast, maybe you should try this one instead — newyuppie, May 29 '11 at 21:03

score 4 · Accepted Answer · answered May 29 '11 at 01:05

4

If id is the primary key of the table, then this will do:

SELECT elo
     , date(date_calculated) AS date
FROM users_historical_elo 
  JOIN
    ( SELECT MAX(id) AS id
      FROM users_historical_elo
      WHERE uid =36
      GROUP BY date(date_calculated)
    ) AS grp
    ON grp.id = users_historical_elo.id
ORDER BY grp.id ASC

answered May 29 '11 at 01:05

ypercubeᵀᴹ

113,259
19
174
235

for moral extra credit ;) how could I modify this query if I had the same situation, but I needed to select the *second-to-last* elo grouped by date? – newyuppie May 29 '11 at 21:01

score 0 · Answer 2 · answered May 29 '11 at 01:02

0

Try:

SELECT elo, date(date_calculated) date FROM users_historical_elo WHERE uid =36 group by date order by id asc

answered May 29 '11 at 01:02

kevinAlbs

1,114
2
11
20

score 0 · Answer 3 · answered May 29 '11 at 01:03

0

I think you need to use MAX and GROUP By. See http://www.tutorialspoint.com/mysql/mysql-max-function.htm

I think this will work

SELECT elo, MAX(date(date_calculated)) date 
FROM users_historical_elo 
WHERE uid =36 
GROUP BY elo 
ORDER BY id asc

answered May 29 '11 at 01:03

cordsen

1,691
12
10

score 0 · Answer 4 · answered May 29 '11 at 01:04

0

This:

 SELECT elo, MAX(date) FROM users_historical_elo

Would return the elo and the last date for any elo, which seems to be what you are asking for?

answered May 29 '11 at 01:04

Rob

11,446
7
39
57

MySQL query to select only last rows of groups of dates

4 Answers4