0

I have a table which has columns version(timestamp), Id , Name , Date etc.

I want to get the row with the latest timestamp which I can get easily using a where condition as below:

where version = (SELECT MAX(version)
                 FROM table t2
                 WHERE t2.Id = t1.Id)

But I am stuck if 2 rows have the same timestamp and different Date column values, then I need to select the row with the latest Date column.

How do I get the latest row in that situation?

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
Yash Depani
  • 1
  • 2
    Please add a few rows of sample data and the expected result. – The Impaler Mar 02 '23 at 16:20
  • 1
    typically timestamp does not mean time of day -- it will be different on different days. What is the actual data type of the column. – Hogan Mar 02 '23 at 16:22
  • 1
    If your `Date` column is unrelated to your `timestamp` column, which already implies a date and time, then how about: **select * from my_table order by version desc,`Date` desc limit 1**? – Booboo Mar 02 '23 at 16:34
  • see the documentation, the code you show should work fine. https://dev.mysql.com/doc/refman/8.0/en/datetime.html – Hogan Mar 02 '23 at 16:36
  • What version if MySQL? If you're still on 5.7 this is MUCH harder. – Joel Coehoorn Mar 02 '23 at 16:51
  • This is the sample data Version Id EndDate ….. 02/20/2023 19:42 xyz 03/01/2023 0:00 02/20/2023 19:42 xyz 03/02/2024 0:00 01/11/2023 12:51 xyz 01/09/2022 0:00 Id here is foreign key , You can see here 2 latest rows are having same version , but there end date are different , So I want the latest end date in this case. @Joel – Yash Depani Mar 03 '23 at 03:36

1 Answers1

0

You can use the row_number() windowing function to do this. The trick with windowing functions is you need to nest them in subquery in order to target the results. This means I need to show more of the query to demonstrate it, and since the code in the question is incomplete, the sample for this answer will need to be also:

SELECT <columns>
FROM (
    SELECT <columns>,
        row_number() over (order by version desc, `date` desc)
    FROM `table`
) t
WHERE rn = 1

There is another technique that can be useful for this. It's usually slower, but it's sometimes easier to reason about to get the query right, especially if you have several different items you need to filter this way. It's called a LATERAL JOIN. Unfornately, the MySQL implementation and documentation on this is kind of awkward, so I'm not gonna try it without knowing more of the actual query.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • This is the sample data Version Id EndDate ….. 02/20/2023 19:42 xyz 03/01/2023 0:00 02/20/2023 19:42 xyz 03/02/2024 0:00 01/11/2023 12:51 xyz 01/09/2022 0:00 Id here is foreign key , You can see here 2 latest rows are having same version , but there end date are different , So I want the latest end date in this case. @Joel – Yash Depani Mar 03 '23 at 03:32