1
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
1
  • 1
    which rdms database do you use aand whuch version of it
    – nbk
    Feb 17, 2021 at 17:50

1 Answer 1

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.