Unique list with the most recent date and the associated value

[mohebk]

Hi,
I have a table that looks like this;
KeyField CPT DESCRIPTION EFFDATE AMT
1 10006 PARONYCHIA,COMPLEX 01-Jan-96 $110.00
2 10007 PARONYCHIA,SIMPLE 01-Jan-96 $70.00
3 10021 FINE NEEDLE ASPIRATION;W/O IMAGING 01-May-06 $239.00
4 10033 DIGITAL BLOCK 01-Jan-96 $26.00
5 10040 ACNE SURGERY 01-Jan-96 $80.00
6 10040 ACNE SURGERY 01-May-06 $155.00
7 10046 FOREIGN BODY REMOVAL, CORNEAL 01-Jan-96 $75.00
9 10060 INCISION & DRAINAGE ABSCESS, SIMPLE/SINGLE 01-Jan-96 $150.00
8 10060 INCISION & DRAINAGE ABSCESS, SIMPLE/SINGLE 01-Apr-97 $110.00
10 10060 INCISION & DRAINAGE ABSCESS, SIMPLE/SINGLE 01-Jan-05 $169.00
11 10060 INCISION & DRAINAGE ABSCESS, SIMPLE/SINGLE 01-May-06 $174.00

And looking to get the output like this:

KeyField CPT DESCRIPTION EFFDATE AMT
1 10006 PARONYCHIA,COMPLEX 01-Jan-96 $110.00
2 10007 PARONYCHIA,SIMPLE 01-Jan-96 $70.00
3 10021 FINE NEEDLE ASPIRATION;W/O IMAGING 01-May-06 $239.00
4 10033 DIGITAL BLOCK 01-Jan-96 $26.00
6 10040 ACNE SURGERY 01-May-06 $155.00
7 10046 FOREIGN BODY REMOVAL, CORNEAL 01-Jan-96 $75.00
11 10060 INCISION & DRAINAGE ABSCESS, SIMPLE/SINGLE 01-May-06 $174.00



I tried to use the group by but it does not work because of the different amt values. I can't use first or last because that may lose the date/amt association. Can you please advice on a query that I can run to get the data in that format?

Thanks a lot



Mo

 

[PHV]

A starting point (SQL code):

SELECT A.*
FROM yourTable AS A INNER JOIN (
SELECT CPT, Max(EFFDATE) AS LastDate FROM yourTable GROUP BY CPT
) AS M ON A.CPT = M.CPT AND A.EFFDATE = M.LastDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mohebk]

That looks like it worked.
Thanks a lot for your help.

Mo