Mysql Question posted in
The official MySQL documentation can be found here.

mysql select based on latest date

I have a table with non-unique value and date on it
How to select values by the given tuple with the latest datetime?

given tuple (foo, bar)
result should be
[(1, foo, 2024-05-29 03:38:12), (6, bar, 2024-05-29 08:36:14)] 

 id     value   date
 1      foo     2024-05-29 03:38:12*
 2      foo     2024-05-27 12:37:06
 3      xxx     2024-05-29 08:17:24
 4      foo     2024-05-27 13:45:03
 5      bar     2024-05-28 12:56:04
 6      bar     2024-05-29 08:36:14*

I tried with the MAX(date) and GROUP BY, but seems my mysql level not that good to get a result((
Thank you

Tags:

2

Answers


  1. 0

    On MySQL 8+ the ROW_NUMBER() window function provides one way to do this:

    WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY value ORDER BY date DESC) rn
    FROM yourTable
)

SELECT id, value, date
FROM cte
WHERE value IN ('foo', 'bar') AND rn = 1;

    Here is a version which would work on earlier versions of MySQL (5.7 and lower):

    SELECT t1.id, t1.value, t1.date
FROM yourTable t1
INNER JOIN (
    SELECT value, MAX(date) AS max_date
    FROM yourTable
    GROUP BY value
) t2
    ON t2.value = t1.value AND
       t2.max_date = t1.date
WHERE
    t1.value IN ('foo', 'bar');
    Login or Signup to reply.
  2. 0 
      SELECT t.id, t.value, t.date
FROM your_table t
INNER JOIN (
    SELECT value, MAX(date) AS max_date
    FROM your_table
    GROUP BY value
) AS latest ON t.value = latest.value AND t.date = latest.max_date;

    SELECT value, MAX(date) AS max_date FROM your_table GROUP BY value: This subquery calculates the latest date for each value across the entire table.

    SELECT t.id, t.value, t.date FROM your_table t: This is selecting the columns you are interested in from your table.

    INNER JOIN (…subquery…) AS latest ON t.value = latest.value AND t.date = latest.max_date: This join ensures that you only get the rows that match the latest date for each value as found in the subquery.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search