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

ID : 20209

viewed : 23

Tags : sqlpuzzlesql

Top 5 Answer for puzzle - What is the simplest SQL Query to find the second largest value?

vote vote

100

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

82

SELECT MAX(col)  FROM table  WHERE col NOT IN ( SELECT MAX(col)                     FROM table                  ); 
vote vote

77

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) topTwo order by col desc  

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.

Alternatively, in Microsoft SQL 2005 and above you can use the ROW_NUMBER() function:

select col from (     select ROW_NUMBER() over (order by col asc) as 'rowNum', col     from [table] ) withRowNum  where rowNum = 2 
vote vote

68

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 see 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 (pre-2012):

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 

Update:

SQL Server 2012 now supports a much cleaner (and standard) OFFSET/FETCH syntax:

SELECT [column]  FROM [Table]  GROUP BY [column]  ORDER BY [column] DESC OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY; 
vote vote

55

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.

Top 3 video Explaining puzzle - What is the simplest SQL Query to find the second largest value?

Related QUESTION?