I need help with my query to use SELECT MAX or SELECT TOP? 1

[ayh8disjob]

I need some help here i have this TABLE1 and
I need to display the value of F1 and FSTA
of the latest FDATE for each group of F2....
TABLE1:
F1 F2 FDATE FSTA
--- --- ----------- ----
179 2 2003-11-22 1
222 2 2002-12-01 1
180 5 2002-11-22 1
181 5 2003-11-22 1
182 13 2003-01-10 1
185 13 2002-12-12 1
191 20 2003-11-22 1
197 20 2002-12-22 1
188 24 2003-09-05 1
189 24 2003-11-22 1
190 25 2003-11-22 1
192 25 2003-10-22 1
198 26 2002-12-22 1
------------------------

QUERY OUTPUT:
F1 F2 FDATE FSTA
--- --- ----------- ----
179 2 2003-11-22 1
181 5 2003-11-22 1
185 13 2002-12-12 1
191 20 2003-11-22 1
189 24 2003-11-22 1
190 25 2003-11-22 1
198 26 2002-12-22 1
------------------------
Thanks you very much in advance...

really having problem som tough time with this

 

[mwolf00]

SELECT myTab.f1, derivedTable.f2, derivedTable.maxDate, myTab.fsta FROM myTable myTab JOIN
(SELECT f2, maxDate=MAX(fDate) FROM myTable GROUP BY f2) as derivedTable ON myTab.f2 = derivedTable.f2 AND myTab.fDate = derivedTable.maxDate ORDER BY myTab.f1 -----------------------------------------------------------------
"Whether you think that you can, or that you can't, you are usually right."
- Henry Ford (1863-1947)

mikewolf@tst-us.com

 

[LFCfan]

select F1, F2, FDATE, FSTA
from TABLE1
where FDATE = (select max(FDATE) from TABLE1)
order by F1 asc

This would give you this output though (only for the latest date, ie 2003-11-22)

F1 F2 FDATE FSTA
--- --- ----------- ----
179 2 2003-11-22 1
181 5 2003-11-22 1
189 24 2003-11-22 1
190 25 2003-11-22 1
191 20 2003-11-22 1


Hope this helps
Maria

 

[ayh8disjob]

Thanks for the help!