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

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.

From stackoverflow Niyaz

• 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
  

  Craig : Have you run this on SQL Server?
  From Magnar

• 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.

  From dguaraglia

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

  From Matt Rogish

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

  From Chris Conway

• This works in MS SQL:

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

  From Tom Welch

• 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
  

  From doekman

• 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.

  From Keith

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

  Sybase SQL Anywhere supports:

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

  From Graeme Perrow

• 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
  

  From Joel Coehoorn

• 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.

  From $pageUsers[$post.author].name

• Using a correlated query:

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

  From $pageUsers[$post.author].name