SQL Only display data with Max latest date date

datefiltered-indexmax

tracking_id | date_action | action_id
1 | 2021/01/24 | 4
1 | 2021/01/26 | 3
2 | 2021/01/25 | 67
2 | 2021/01/24 | 8
4 | 2021/01/27 | 10
5 | 2021/01/24 | 6
4 | 2021/01/25 | 55

Result should be:

tracking_id | date_action | action_id
1 | 2021/01/26 | 3
2 | 2021/01/25 | 67
4 | 2021/01/27 | 10

Best Answer

This Version is for MySQL, but it should work on any rdms The subselect get the Max date from the table for every track_id and uses that ti get the wanted row.

But this would select all rows for that track_id, that have that max date.

If you only want you, you need row_number and an Order

CREATE TABLE tracks
    (`tracking_id` int, `date_action` datetime, `action_id` int)
;
    
INSERT INTO tracks
    (`tracking_id`, `date_action`, `action_id`)
VALUES
    (1, '2021-01-24 01:00:00', 4),
    (1, '2021-01-26 01:00:00', 3),
    (2, '2021-01-25 01:00:00', 67),
    (2, '2021-01-24 01:00:00', 8),
    (4, '2021-01-27 01:00:00', 10),
    (5, '2021-01-24 01:00:00', 6),
    (4, '2021-01-25 01:00:00', 55)
;

SELECT * 
FROM tracks t1
WHERE `date_action` = (SELECT MAX(`date_action`) FROM tracks WHERE `tracking_id` = t1.`tracking_id`)

tracking_id | date_action         | action_id
----------: | :------------------ | --------:
          1 | 2021-01-26 01:00:00 |         3
          2 | 2021-01-25 01:00:00 |        67
          4 | 2021-01-27 01:00:00 |        10
          5 | 2021-01-24 01:00:00 |         6

db<>fiddle here

And here the same for Postgres

CREATE TABLE tracks
    ("tracking_id" int, "date_action" timestamp, "action_id" int)
;
    
INSERT INTO tracks
    ("tracking_id", "date_action", "action_id")
VALUES
    (1, '2021-01-24 01:00:00', 4),
    (1, '2021-01-26 01:00:00', 3),
    (2, '2021-01-25 01:00:00', 67),
    (2, '2021-01-24 01:00:00', 8),
    (4, '2021-01-27 01:00:00', 10),
    (5, '2021-01-24 01:00:00', 6),
    (4, '2021-01-25 01:00:00', 55)
;

SELECT * 
FROM tracks t1
WHERE "date_action" = (SELECT MAX("date_action") FROM tracks WHERE "tracking_id" = t1."tracking_id")

tracking_id | date_action         | action_id
----------: | :------------------ | --------:
          1 | 2021-01-26 01:00:00 |         3
          2 | 2021-01-25 01:00:00 |        67
          4 | 2021-01-27 01:00:00 |        10
          5 | 2021-01-24 01:00:00 |         6

db<>fiddle here

andd a Version of it for SQL Server

CREATE TABLE tracks
    ("tracking_id" int, "date_action" DATETIME, "action_id" int)
;
    
INSERT INTO tracks
    ("tracking_id", "date_action", "action_id")
VALUES
    (1, '2021-01-24 01:00:00', 4),
    (1, '2021-01-26 01:00:00', 3),
    (2, '2021-01-25 01:00:00', 67),
    (2, '2021-01-24 01:00:00', 8),
    (4, '2021-01-27 01:00:00', 10),
    (5, '2021-01-24 01:00:00', 6),
    (4, '2021-01-25 01:00:00', 55)
;

SELECT * 
FROM tracks t1
WHERE "date_action" = (SELECT MAX("date_action") FROM tracks WHERE "tracking_id" = t1."tracking_id")
GO

tracking_id | date_action             | action_id
----------: | :---------------------- | --------:
          5 | 2021-01-24 01:00:00.000 |         6
          4 | 2021-01-27 01:00:00.000 |        10
          2 | 2021-01-25 01:00:00.000 |        67
          1 | 2021-01-26 01:00:00.000 |         3

db<>fiddle here

Related Solutions

Mysql – How to i return the date of max id

You don't need a subquery for this. It can be simple and more efficient:

SELECT id_product, date  
FROM products
WHERE fk_stat = 1
  AND id_order = 77185 
ORDER BY id_product DESC
LIMIT 1 ;

Mysql – MIN / MAX values with Date

Your query is invalid SQL, but MySQL accepts it with the default settings. It will give you a random result. You can change this behavior by adding 'ONLY_FULL_GROUP_BY' to sql_mode.

EDIT: Similar to @MickyT's solution but using a join instead:

SELECT M.*
FROM WEATHER_MEASUREMENT M
JOIN (
   SELECT MONTH(CREATED) AS MONTH
        , MIN(AMBIENT_TEMPERATURE) AS MIN_TEMP
        , MAX(AMBIENT_TEMPERATURE) AS MAX_TEMP
   FROM WEATHER_MEASUREMENT M
   GROUP BY MONTH(CREATED)
) X
    ON MONTH(M.CREATED) = X.MONTH
   AND M.AMBIENT_TEMPERATURE IN (X.MIN_TEMP, X.MAX_TEMP);

It is not clear what time that should be used in case of a draw, the query outputs all times where a min or max temp occurred. Assuming that you would like the first occurrence, something like:

SELECT MIN(M.CREATED), M.AMBIENT_TEMPERATURE
FROM WEATHER_MEASUREMENT M
JOIN (
   SELECT MONTH(CREATED) AS MONTH
        , MIN(AMBIENT_TEMPERATURE) AS MIN_TEMP
        , MAX(AMBIENT_TEMPERATURE) AS MAX_TEMP
   FROM WEATHER_MEASUREMENT M
   GROUP BY MONTH(CREATED)
) X
    ON MONTH(M.CREATED) = X.MONTH
   AND M.AMBIENT_TEMPERATURE IN (X.MIN_TEMP, X.MAX_TEMP);
GROUP BY M.AMBIENT_TEMPERATURE

can be used.

I left out the predicate for the current month, but that is easily added. Since MySQL does not support LATERAL join, it is probably better to add that predicate to the derived table as well. I.e.

SELECT MIN(M.CREATED), M.AMBIENT_TEMPERATURE
FROM WEATHER_MEASUREMENT M
JOIN (
   SELECT MONTH(CREATED) AS MONTH
        , MIN(AMBIENT_TEMPERATURE) AS MIN_TEMP
        , MAX(AMBIENT_TEMPERATURE) AS MAX_TEMP
   FROM WEATHER_MEASUREMENT M
   WHERE (MONTH(DATE(CREATED)) = Month(NOW()))
   GROUP BY MONTH(CREATED)
) X
    ON MONTH(M.CREATED) = X.MONTH
   AND M.AMBIENT_TEMPERATURE IN (X.MIN_TEMP, X.MAX_TEMP);
WHERE (MONTH(DATE(CREATED)) = Month(NOW()))
GROUP BY M.AMBIENT_TEMPERATURE

As @RolandoMySQLDBA points out the WHERE clause can be rewritten in a index friendlier way.

Best Answer

Related Solutions

Mysql – How to i return the date of max id

Mysql – MIN / MAX values with Date

Related Question