Pick latest records 1

[Andrzejek]

I have this data in tableA:

[pre]
PARCEL_KEY ADJ_DATE ADJ_TOTAL[blue]
5183 11/10/2021 2:53:39 PM -12640[/blue]
5183 11/10/2021 2:52:52 PM -13640[blue]
12420 8/20/2021 10:32:45 AM -23
14647 11/9/2021 1:13:21 PM -1500
18445 11/10/2021 12:51:33 PM -850[/blue]
18445 11/10/2021 12:14:32 PM -750
18445 11/10/2021 12:04:28 PM -500[blue]
19705 8/6/2021 11:27:03 AM 1054
25880 11/8/2021 7:58:56 AM -100
26824 8/6/2021 11:31:08 AM -39748
27612 11/10/2021 10:12:46 AM -79334[/blue]
27612 11/10/2021 8:27:24 AM -78230[/pre]

I would like to select [blue]BLUE[/blue] records only, which are records with the latest ADJ_DATE per PARCEL_KEY field.
Actually, I just need PARCEL_KEY and ADJ_TOTAL fields from the blue records.

I know I can ask for MAX(ADJ_DATE)... GROUP BY PARCEL_KEY, but how can I get the ADJ_TOTAL for those records?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[carp]

Something like this:

SELECT parcel_key, adj_total
FROM tableA a
     INNER JOIN (SELECT parcel_key, MAX(adj_date) AS adate
                   FROM tableA
                  GROUP BY parcel_key) v
     ON a.parcel_key = v.parcel_key
        AND a.adj_date = v.adate;

 

[Andrzejek]

Thanks carp,
After rocking my brain yesterday, I consulted with my co-worker and he came up with the same solution.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson