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

SELECT MAX( col )
  FROM table
 WHERE col < ( 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.

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

select col_name
from (
    select dense_rank() over (order by col_name desc) as 'rank', col_name
    from table_name ) withrank 
where rank = 2

select max(column_name) 
from table_name
where column_name not in ( select max(column_name) 
                           from table_name
                         );

not in is a condition that exclude the highest value of column_name.

Reference : programmer interview

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

This works in MS SQL:

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

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

See How to select the nth row in a SQL database table?.

Sybase SQL Anywhere supports:

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

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.

select * from [table] where (column)=(select max(column)from [table] where column < (select max(column)from [table]))

 SELECT  * FROM `employee` WHERE  employee_salary = (SELECT employee_salary 
 FROM`employee` GROUP BY employee_salary ORDER BY employee_salary DESC LIMIT 
 1,1)

At first make a dummy table without max salary then query max value from dummy table

SELECT max(salary) from (Select * FROM emp WHERE salary<> (SELECT MAX(salary) from emp)) temp