How do I get several highest values from a table?

Question

I have a table like

id  f1
--------------
1   2000-01-01
1   2001-01-01
1   2002-01-01
1   2003-01-01

And I want to get say the latest 3 dates in one row

CREATE TABLE Test
(
  id INT NOT NULL,
  f1 DATETIME NOT NULL,
)

INSERT INTO Test (id, f1) VALUES (1, '1/1/2000')
INSERT INTO Test (id, f1) VALUES (1, '1/1/2001')
INSERT INTO Test (id, f1) VALUES (1, '1/1/2002')
INSERT INTO Test (id, f1) VALUES (1, '1/1/2003')

SELECT T1.* FROM Test as T1

Was trying something like

         SELECT T1.*,T2.* 
           FROM Test AS T1
LEFT OUTER JOIN Test AS T2 ON T1.id = T2.id AND (T2.f1 > T1.f1)

Answer 1

Although I'm not sure how to get them into a single row, you could start with:

SELECT * FROM Test ORDER BY f1 DESC LIMIT 3

That should give you a result like:

id  f1
1   2003-01-01
1   2002-01-01
1   2001-01-01

Putting them into a single row, though, may be a bit more difficult...

Answer 2

In sql server you could do select top 3 * from Test order by f1 desc. Other DBMS's have similar posibilities such as MySql's limit, Oracle's rownum etc.