[SOLVED] Retrieve latest Rows in table

Retrieve latest Rows in table

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

Solution

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