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);"