What is the simplest SQL Query to find the second largest value?

Question

What is the simplest SQL query to find the second largest integer value in a specific column? Of course there may be duplicate values in the column.

Answer 1

The easiest would be to get the second value from this result set in the application:

SELECT DISTINCT value FROM Table ORDER BY value DESC LIMIT 2

But if you must select the second value using SQL, how about:

SELECT MIN(value) FROM (SELECT DISTINCT value FROM Table ORDER BY value DESC LIMIT 2) AS t

Answer 2

I suppose you can do something like:

SELECT * FROM Table ORDER BY NumericalColumn DESC LIMIT 1 OFFSET 1

or

SELECT * FROM Table ORDER BY NumericalColumn DESC LIMIT (1, 1)

depending on your database server. Hint: SQL Server doesn't do LIMIT.

Answer 3

SELECT MAX( col )
  FROM table
 WHERE col < ( SELECT MAX( col )
                 FROM table )

Answer 4

select top 1 MyIntColumn from MyTable
where
 MyIntColumn <> (select top 1 MyIntColumn from MyTable order by MyIntColumn desc)
order by MyIntColumn desc

Answer 5

This works in MS SQL:

select max([COLUMN_NAME]) from [TABLE_NAME] where [COLUMN_NAME] < 
 ( select max([COLUMN_NAME]) from [TABLE_NAME] )

Answer 6

Something like this? I haven't tested it, though:

select top 1 x
from (
  select top 2 distinct x 
  from y 
  order by x desc
) z
order by x

Answer 7

In T-Sql there are two ways:

--filter out the max
select max( col )
from [table]
where col < ( 
    select max( col )
    from [table] )

--sort top two then bottom one
select top 1 col 
from (
    select top 2 col 
    from [table]
    order by col desc ) topTwo
order by col

In Microsoft SQL the first way is twice as fast as the second, even if the column in question is clustered.

This is because the sort operation is relatively slow compared to the table or index scan that the max aggregation uses.

Answer 8

See http://stackoverflow.com/questions/16568.

Sybase SQL Anywhere supports:

SELECT TOP 1 START AT 2 value from table ORDER BY value

Answer 9

I see both some SQL Server specific and some MySQL specific solutions here, so you might want to clarify which database you need. Though if I had to guess I'd say SQL Server since this is trivial in MySQL.

I also some solutions that won't work because they fail to take into account the possibility for duplicates, so be careful which ones you accept. Finally, I see a few that will work but that will make two complete scans of the table. You want to make sure the 2nd scan is only looking at 2 values.

SQL Server:

SELECT MIN([column]) AS [column]
FROM (
    SELECT TOP 2 [column] 
    FROM [Table] 
    GROUP BY [column] 
    ORDER BY [column] DESC
) a

MySQL:

SELECT `column` FROM `table` GROUP BY `column` ORDER BY `column` DESC LIMIT 1,1

Answer 10

select * from emp e where 3>=(select count(distinct salary)
    from emp where s.salary<=salary)

This query selects the maximum three salaries. If two emp get the same salary this does not affect the query.

Answer 11

Using a correlated query:

Select * from x x1 where 1 = (select count(*) from x where x1.a < a)