Sql 2000

selecting a distinct record

[shilpij]

I have a table which has the combination of
f_srl_no, year and med_dt as primary key.
combination of f_srl_no and year can repeat but med_dt for the combination is unique. I want to select the records for only the latest med_dt for the combination of f_srl_no and year.

There can be many records with the combination but each will have different med_dt. e.g

f_srl_no year med_dt
100 2000 10/10/2003
100 2000 5/5/2004
2 2004 4/6/2004

I want an output where only the two records should show, one for 2-2004 and second for 100-2000 with med_dt as 5/5/2004 (the latest one only).
My database is on SQL 2000.

[IanTurner]

You need something like this

Select Max(med_dt), f_srl_no, year from my_table
Group By f_srl_no, year
Order by Max(med_dt) Desc

[shilpij]

Thanx a lot Ian, It worked. The solution seems so simple and I SPENT SO MUCH TIME ON IT