Helpful Information
 
 
Category: Oracle Development
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
fld1, fld2, max(fld3)
from table1
group by fld1, fld2

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










privacy (GDPR)