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?

There are maybe duplicate values in the column.

Answer 1

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

Answer 2

SELECT MAX(sal) 
FROM emp
WHERE sal NOT IN ( SELECT top 3 sal 
                   FROM emp order by sal desc 
                 )
            

this will return the third highest sal of emp table

Answer 3

select age 
from student 
group by id having age< ( select max(age) 
                          from student 
                        )
order by age 
limit 1

Answer 4

This is an another way to find the second largest value of a column.Consider the table 'Student' and column 'Age'.Then the query is,

select top 1 Age 
from Student 
where Age in ( select distinct top 2 Age  
               from Student order by Age desc 
             ) order by Age asc

Answer 5

select max(COL_NAME) 
from TABLE_NAME 
where COL_NAME in ( select COL_NAME 
                    from TABLE_NAME 
                    where COL_NAME < ( select max(COL_NAME) 
                                       from TABLE_NAME
                                      )
                   );

subquery returns all values other than the largest. select the max value from the returned list.

Answer 6

Tom, believe this will fail when there is more than one value returned in select max([COLUMN_NAME]) from [TABLE_NAME] section. i.e. where there are more than 2 values in the data set.

Slight modification to your query will work -

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

Answer 7

Simplest of all

select sal 
from salary 
order by sal desc 
limit 1 offset 1

Answer 8

SELECT MAX(Salary) 
FROM Employee 
WHERE Salary NOT IN ( SELECT MAX(Salary) 
                      FROM Employee 
                    )

This query will return the maximum salary, from the result - which not contains maximum salary from overall table.

Answer 9

A very simple query to find the second largest value

SELECT `Column` 
FROM `Table` 
ORDER BY `Column` DESC 
LIMIT 1,1;

Answer 10

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 11

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 12

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

Answer 13

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;

Answer 14

Use this query.

SELECT MAX( colname ) 
FROM Tablename 
where colname < (
    SELECT MAX( colname ) 
    FROM Tablename)

Answer 15

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

Answer 16

You can find nth highest value using the following query.

    select top 1 UnitPrice from (select distinct top n UnitPrice from 
[Order Details] order by UnitPrice desc) as Result order by UnitPrice asc

Here, the value of n will be 1 (for the highest number), 2 (for the second highest number), 3 (for the third highest number)...

Answer 17

This is very simple code, you can try this :-

ex : Table name = test

salary 

1000
1500
1450
7500

MSSQL Code to get 2nd largest value

select salary from test order by salary desc offset 1 rows fetch next 1 rows only;

here 'offset 1 rows' means 2nd row of table and 'fetch next 1 rows only' is for show only that 1 row. if you dont use 'fetch next 1 rows only' then it shows all the rows from the second row.

Answer 18

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

Answer 19

MSSQL

SELECT  *
  FROM [Users]
    order by UserId desc OFFSET 1 ROW 
FETCH NEXT 1 ROW ONLY;

MySQL

SELECT  *
  FROM Users
    order by UserId desc LIMIT 1 OFFSET 1

No need of sub queries ... just skip one row and select second rows after order by descending

Answer 20

The easiest way to get second last row from a SQL table is to use ORDER BYColumnNameDESC and set LIMIT 1,1.

Try this:

SELECT * from `TableName` ORDER BY `ColumnName` DESC LIMIT 1,1

Answer 21

Very Simple. The distinct keyword will take care of duplicates as well.

SELECT distinct SupplierID FROM [Products] order by SupplierID desc limit 1 offset 1

Answer 22

As you mentioned duplicate values . In such case you may use DISTINCT and GROUP BY to find out second highest value

Here is a table

salary

:

GROUP BY

SELECT  amount FROM  salary 
GROUP by amount
ORDER BY  amount DESC 
LIMIT 1 , 1

DISTINCT

SELECT DISTINCT amount
FROM  salary 
ORDER BY  amount DESC 
LIMIT 1 , 1

First portion of LIMIT = starting index

Second portion of LIMIT = how many value

Answer 23

select extension from [dbo].[Employees] order by extension desc offset  2  rows fetch next  1 rows only

Answer 24

Old question I know, but this gave me a better exec plan:

 SELECT TOP 1 LEAD(MAX (column)) OVER (ORDER BY column desc)
 FROM TABLE 
 GROUP BY column

Answer 25

Microsoft SQL Server - Using Two TOPs for the N-th highest value (aliased sub-query).

To solve for the 2nd highest:

SELECT TOP 1 q.* 
FROM (SELECT TOP 2 column_name FROM table_name ORDER BY column_name DESC) as q
ORDER BY column_name ASC;

Uses TOP twice, but requires an aliased sub-query. Essentially, the inner query takes the greatest 2 values in descending order, then the outer query flips in ascending order so that 2nd highest is now on top. The SELECT statement returns this top.

To solve for the n-th highest value modify the sub-query TOP value. For example:

SELECT TOP 1 q.* 
FROM (SELECT TOP 5 column_name FROM table_name ORDER BY column_name DESC) as q
ORDER BY column_name;

Would return the 5th highest value.

Answer 26

select score 
from table 
where score = (select max(score)-1 from table)

Answer 27

select MAX(salary) as SecondMax from test where salary !=(select MAX(salary) from test)

Answer 28

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

Answer 29

Try:

select a.* ,b.* from 
(select * from (select ROW_NUMBER() OVER(ORDER BY fc_amount desc) SrNo1, fc_amount as amount1 From entry group by fc_amount) tbl where tbl.SrNo1 = 2) a
,
(select * from (select ROW_NUMBER() OVER(ORDER BY fc_amount asc) SrNo2, fc_amount as amount2  From entry group by fc_amount) tbl where tbl.SrNo2 =2) b

Answer 30

It is the most esiest way:

SELECT
      Column name
FROM
      Table name 
ORDER BY 
      Column name DESC
LIMIT 1,1