Get latest value from same table by using join

My table is having values. Can i have help please:

Ref Name Operation Date

M-0044 Roberto Put up 2016-01-13
M-0044 ABC Sold 2016-01-23
M-0044 XYZ Removed 2016-03-07
M-0044 X123 To Let 2016-01-13
M-0044 Y123 Put Up 2016-02-13

M-0041 Roberto Put up 2016-01-13
M-0041 ABC Sold 2016-01-23
M-0041 XYZ Removed 2016-03-09
M-0041 X123 To Let 2016-01-13
M-0041 Y123 Put Up 2016-01-13

M-0042 Y123 Put Up 2016-02-13

M-0043 Y123 Put Up 2016-02-13

I will use group by Ref so that if any reference having latest record as operation=Removed then don’t select any record of that reference so result should be:

M-0042 Y123 Put Up 2016-02-13
M-0043 Y123 Put Up 2016-02-13

SELECT mytable.Ref , mytable.Name , mytable.Operation , mytable.Date FROM ( SELECT Ref , MAX(`Date`) AS latest FROM mytable GROUP BY Ref ) AS maxes INNER JOIN mytable AS checkop ON checkop.Ref = maxes.Ref AND checkop.Date = maxes.latest INNER JOIN mytable ON mytable.Ref = checkop.Ref WHERE checkop.Operation <> 'Removed'

2 Likes

Great many thanks.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.