Retrieve latest Rows in table [duplicate]

Question

Sample data

AIID	NIC	Status	Remarks	ID	ADT
28	9595	ON THE WAY	NO	8	2024-06-06 16:02:33
27	9595	ABSENT	NO	8	2024-05-20 16:02:33
26	573	VISITED	NO	10	2024-05-09 15:31:55
25	573	ON THE WAY	NO	10	2024-05-05 13:17:52
24	573	ABSENT	NO	10	2024-05-05 13:17:31
23	9595	VISITED	NO	8	2024-05-04 15:50:40
22	9595	ON THE WAY	NO	8	2024-05-04 15:50:25
21	1999	VISITED	NO	7	2024-05-03 19:29:26
20	1999	ON THE WAY	NO	7	2024-05-03 19:29:11

I want to get last updated status in this table..

what I want :

WHAT I TRIED

SELECT
  u1.NIC,
  u1.Status,
  u1.Remarks,
  u1.ADT
FROM callsc2 u1
  LEFT OUTER JOIN callsc2 u2
    ON u1.NIC = u2.NIC
    AND u1.ADT > u2.ADT

Please share your simple data as text not images, so it can be easy for us to help — SelVazi
– SelVazi, Commented Jun 6, 2024 at 10:35
You were almost there with your query. You just needed to reverse the ADT comparison and add WHERE u2.AIID IS NULL - db<>fiddle. The approach shown by SelVazi should still be the more efficient method, especially with an index on (NIC, ADT). — user1191247
– user1191247, Commented Jun 6, 2024 at 12:04

SelVazi · Accepted Answer · 2024-06-06 10:43:29Z

0

This can be done by joining your table with a dataset containing max ADT per status :

SELECT u.*
FROM callsc2 u
INNER JOIN (
  SELECT NIC, MAX(ADT) AS MAX_ADT
  FROM callsc2 
  GROUP BY NIC
) AS s on s.NIC = u.NIC AND s.MAX_ADT = u.ADT

answered Jun 6, 2024 at 10:43

SelVazi

16.2k2 gold badges20 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1