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.

Answer 3

You can do this with a combination of ORDER BY, TOP and PIVOT, at least on SQL Server. It seems that many of the other answers have ignored the need for the result to be "all on one row."

Answer 4

You can get top 3 dates in a row by pivoting the table and perhaps concatenating the dates if you want them in one column after pivoting.

Edit : here is a query to pivot the table and provide the latest 3 dates in a row. But to pivot you would need to know the data that is available in the table. I figured since we are querying for the latest 3 dates we will not know the exact vales to pivot around the date column. So First, I queried the latest 3 dates into a temp table. Then ran a pivot on the row_number 1, 2 and 3 to obtain the latest 3 dates in a row.

Select Top 3 * into #Temp from Test order by f1 desc 

Now, pivot on the row_number column -

SELECT id,[3] as Latest,[2] as LatestMinus1,[1] as LatestMinus2
FROM (
select ROW_NUMBER() OVER(ORDER BY f1) AS RowId,f1,id from #Temp) AS Src
PIVOT (Max(f1) FOR RowId IN ([1],[2],[3])) AS pvt

This results in -

Id | Latest                  |LatestMinus1             |LatestMinus2
1  | 2003-01-01 00:00:00.000 | 2002-01-01 00:00:00.000 | 2001-01-01 00:00:00.000

And, of course

drop table #Temp

Answer 5

What you are trying to do is called pivot table, here is an article on how to do it:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:766825833740

also, if you use Oracle I would look at analytic functions, OVER PARTITION BY in particular

Answer 6

What about this ?

SELECT T1.f1 as "date 1", T2.f1 as "date 2", T3.f1 as "date 3"
  FROM (SELECT *
         FROM `date_test`
        ORDER BY `f1` DESC
        LIMIT 1) AS T1,
      (SELECT *
         FROM `date_test`
        ORDER BY `f1` DESC
        LIMIT 1, 1) AS T2,
      (SELECT *
         FROM `date_test`
        ORDER BY `f1` DESC
        LIMIT 2, 1) AS T3
;

Which outputs :

+------------+------------+------------+
| date 1     | date 2     | date 3     |
+------------+------------+------------+
| 2003-01-01 | 2002-01-01 | 2001-01-01 |
+------------+------------+------------+

The only downside is that you need at least three rows, otherwise it won't return anything...

Using JOIN, you can do this :

SELECT T1.id, 
       T1.f1 as "date 1", 
       T2.f1 as "date 2", 
       T3.f1 as "date 3"
  FROM `date_test` as T1
  LEFT JOIN (SELECT * FROM `date_test` ORDER BY `f1` DESC) as T2 ON (T1.id=T2.id AND T1.f1 != T2.f1)
  LEFT JOIN (SELECT * FROM `date_test` ORDER BY `f1` DESC) as T3 ON (T1.id=T3.id AND T2.f1 != T3.f1 AND T1.f1 != T3.f1)
 GROUP BY T1.id
 ORDER BY T1.id ASC, T1.f1 DESC

Which will return something like :

+----+------------+------------+------------+
| id | date 1     | date 2     | date 3     |
+----+------------+------------+------------+
| 1  | 2001-01-01 | 2003-01-01 | 2002-01-01 |
+----+------------+------------+------------+

The downside is that date1, date 2 and date 3 will not necessarily be in a specific order (as per the above sample output). But this can be achieved programatically. The plus side is that you can insert a WHERE clause before the GROUP BY and you can search by T1.id, for example.

Answer 7

in T-SQL (This will get you the top three dates even if they are all the same value)

with TestWithRowNums(f1, row_num) as
(
select f1, row_number() over(order by [f1] desc) as row_num from test
)
select
(select [f1] from TestWithRowNums where row_num = 1) as [Day 1],
(select [f1] from TestWithRowNums where row_num = 2) as [Day 2],
(select [f1] from TestWithRowNums where row_num = 3) as [Day 3]

This will get you the top three DISTINCT dates

with TestWithRankNums(f1, rank_num) as
(
select f1, dense_rank() over(order by [f1] desc) as rank_num from test
)
select
(select top 1 [f1] from TestWithRankNums where rank_num = 1) as [Day 1],
(select top 1 [f1] from TestWithRankNums where rank_num = 2) as [Day 2],
(select top 1 [f1] from TestWithRankNums where rank_num = 3) as [Day 3]

Try this in SQL Server 2005

--to get top three values even if they are the same
select [1] as Day1, [2] as Day2, [3] as Day3 from 
(select top 3 f1, row_number() over(order by [f1] desc) as row_num from test) src
pivot
(
max(f1) for row_num in([1], [2], [3])
) as pvt
--to get top three distinct values
select [1] as Day1, [2] as Day2, [3] as Day3 from 
(select f1, dense_rank() over(order by [f1] desc) as row_num from test) src
pivot
(
max(f1) for row_num in([1], [2], [3])
) as pvt