How do i compare rows within the same table & the criteria is to get data by the latest date

[fluffyvampirekitten]

Can anyone help me with this ?

I would like to compare the date with the SAME ID and extract the row based on LV_DATE - the latest date .

Is it possible to do so ?
I tried to do it in query but I couldn't get the result that I want : Below is the code

SELECT a.GROUP_NO , iif( a.LV_DATE < b.LV_DATE , b.LV_DATE , a.LV_DATE ) AS LVDate , b.LV_INS
FROM test4 AS A 
INNER JOIN test4 AS B 
ON (A.LV_INS < B.LV_INS) AND (a.GROUP_NO = b.GROUP_NO)
WHERE ((A.LV_INS) <> (B.LV_INS))  ;

Kindly help Thanks in advance

---

This is the result that I want based on the table below

GROUP_NO	LVDATE	LV_INS
1	01/01/2016	ABC
2	02/02/2016	RED
3	02/02/2016	JIN

---

Here is the sample of my table

GROUP_NO	LVDATE	LV_INS
1	01/01/2015	ABC
1	01/01/2016	ABC
2	02/02/2016	RED
2
3	02/02/2016	JIN
3	02/02/2016	JIN

[CJ_London]

two solutions

SELECT *
FROM test4 as A
WHERE A.LVDate=(SELECT Max(LVDate) FROM test4 WHERE GroupNo=A.GroupNo)

and (probably faster for large datasets)

SELECT A.*
FROM test4 as A INNER JOIN (SELECT GroupNo, Max(LVDate) AS Latest FROM test4 GROUP BY GroupNo) AS B ON A.GroupNo=B.GroupNo AND A.LVDate=B.Latest

[CJ_London]

Just realised you have duplicate records - so change the SELECT to SELECT DISTINCT

[fluffyvampirekitten]

Thank you for helping me . It works . I didn't know can do this way. I think the first one more easier to understand hahaha .