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

Question

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

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

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

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;

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.

you can find the second largest value of column by using the following query

SELECT *
FROM TableName a
WHERE
  2 = (SELECT count(DISTINCT(b.ColumnName))
       FROM TableName b WHERE
       a.ColumnName <= b.ColumnName);

you can find more details on the following link

http://www.abhishekbpatel.com/2012/12/how-to-get-nth-maximum-and-minimun.html