We help IT Professionals succeed at work.

SQL Server 2008

VasuSidhu
VasuSidhu used Ask the Experts™
on
I have a table in sql with 3 columns.

File#      Status      Exportdate
1      a      1-Jul
1      s      9-Jul
1      d      11-Jul
1      f      13-Jul
1      g      15-Jul
1      h      10-Aug
1      j      20-Aug
2      f      2-Jul
2      a      11-Jul
2      s      31-Jul
2      d      9-Aug


I want the status of the file having last date.
for eg the output of the query should be

File#      Status      ExportDate
1      j      20-Aug
2      d      9-Aug
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010
Commented:
SELECT t1.[File#], t1.Status, t1.ExportDate
FROM SomeTable t1 INNER JOIN
    (SELECT t2.[File#], MAX(t2.ExportDate) AS ExportDate
    FROM SomeTable t2
    GROUP BY t2.[File#]) z ON t1.[File#] = z.[File#] AND t1.ExportDate = z.ExportDate
Metehan OzculluSQL Service Manager

Commented:
SELECT TOP 2 * FROM table order by Exportdate desc
Metehan OzculluSQL Service Manager

Commented:
sorry I did it wrong