SELECT DISTINCT on multiple fields

Hi,

I think I'm having difficulty trying to extract records using SELECT DISTINCT. I'm doing SELECT DISTINCT FLD1, FLD2, FLD3 and I get the following,

FLD1 FLD2 FLD3
---------------------------
Dept1 ABC 04-FEB-03
Dept1 ABC 15-JUN-03
Dept1 ABC 27-AUG-03

Whic is not the result I want. I only need to get the last one with the lastest date which is

Dept1 ABC 27-AUG-03

How can I get the distinct record with the lastest date field ( FLD3 ) when there are multiple records on one field (FLD1) but different date value on other field ( FLD3 )?

TIA for your help,
Scottcka

Hi Scottika ,

u can get ur result this way ....

select distinct T.FLD1,T.FLD2, T.FLD3
from TABLE1 T
where T.FLD3 = (select max(FLD3)
from TABLE1
where FLD1 = T.FLD1)

try it out.

SELECT DISTINCT Field1, Field2 MAX(Field3)
FROM Table
GROUP BY Field1

Hi,

if the data is like this,

ID NAME DAT
--- ------------------------------ ---------
1 aa 12-JAN-06
1 aa 13-JAN-05
2 mm 14-JAN-05
2 mm 04-MAR-06

then use this query to get ur result
" select id,name,dat from test1 where dat in (select max(dat) from test1 group by id,name);"

The result will be :
1 aa 12-jan-06
2 mm 04-mar-06

or if u have data like this

ID NAME DAT
--- ------------------------------ ---------
1 aa 12-JAN-06
1 aa 13-JAN-05
1 aa 14-JAN-05
1 aa 04-MAR-06

" select id,name,dat from test1 where dat = (select max(dat) from test1 group by id,name);"

the result will be:

1 aa 04-mar-06