Select latest record with date and time column with group by clause in mysql

Question

I have this table from which I have to select the latest row on the basis of date and time column for each checkpost

I have tried the following queries but not returning the latest data for each checkpost.

SELECT checkpost_id,current_rate,date,time FROM revisionrates 
WHERE date IN (SELECT max(date) FROM revisionrates GROUP BY checkpost_id)

The expected output is

The specified duplicate is not the same as this question. This question has *two* columns specifying the latest. — Gordon Linoff, Dec 26 '20 at 13:58
Please add code and data as text ([using code formatting](//stackoverflow.com/editing-help#code)), not images. Images: A) don't allow us to copy-&-paste the code/errors/data for testing; B) don't permit searching based on the code/error/data contents; and [many more reasons](//meta.stackoverflow.com/a/285557). Images should only be used, in addition to text in code format, if having the image adds something significant that is not conveyed by just the text code/error/data. — Dharman, Dec 26 '20 at 14:13

score 0 · Answer 1 · answered Dec 26 '20 at 13:55

You can use window functions:

select rr.*
from (select rr.*,
             row_number() over (partition by checkpost_id order by date desc, time desc) as seqnum
      from revisionrates rr
     ) rr
where seqnum = 1;

This requires MySQL 8.0. In earlier versions of MySQL, this is a bit trickier, but one method uses tuples

select rr.*
from rr
where (date, time) in (select rr2.date, rr2.time
                       from revisionrates rr2
                       where rr2.checkpoint_id = rr.checkpoint_id
                       order by rr2.date desc, rr2.time desc
                       limit 1
                      );

Select latest record with date and time column with group by clause in mysql

1 Answers1