How to find second highest or maximum

Nilofers Shaikh

9y1.2k

How to find second highest or second maximum value from table.

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

Poonam Bhosale9y ago
Accepted answer
select max(column_name) from table_name
where column_name < (select max(column_name) from table_name)

For example :-
select max(Salary) from Employee
where Salary < (select max(Salary ) from Employee)
+1
Nilofers Shaikh9y ago
thank u... :)
0
Tushar Dikshit9y ago
Hi,

You can refer following two options for specific second highest or Nth highets. N could be second, third etc.

Find the second highest salary.
Select max(Salary) from employee
where Salary not in (Select max(Salary) from employee)
Now, can you find 3rd, 5th or 6th i.e. N'th highest Salary?
Query for 3rd highest salary
SELECT * FROM EMPLOYEE E
WHERE 2 = (SELECT COUNT(DISTINCT E1.SALARY)
FROM EMPLOYEE E1
WHERE E1.SALARY>E.SALARY)
Here, 2= 3-1 i.e. N-1 ; can be applied for any number.

For more details you can visit C# corner article -

http://www.c-sharpcorner.com/article/most-asked-sql-queries-in-interview-questions/
0
Vighnesh Gadekar9y ago
Hi Amit Arora,

Your query is perfect just change ACS with DESC as mentioned below,
;with temp as
(
Select ROW_NUMBER() Over(order by Salary DESC) as RowNum, Salary from Employee
)
Select Salary from temp where RowNum = 2
0
Amit Arora9y ago
;with temp as
(
Select ROW_NUMBER() Over(order by Salary ASC) as RowNum, Salary from Employee
)
Select Salary from temp where RowNum = 2
0
Ravi Patel9y ago
Hi,

SELECT TOP 1 salary FROM ( SELECT DISTINCT TOP 2 salary FROM employee ORDER BY salary DESC) a ORDER BY salary
0
Vighnesh Gadekar9y ago
Hi Nilofers,

lets consider a Employee table with empid & salary

empid | Salary
=====|=======
1         |100
2         |200
3         |300

if you fire this below query
SELECT TOP 1 Salary AS SecHightestSalary FROM
( SELECT TOP 2 Salary FROM Employee
ORDER BY Salary DESC) AS TempTable ORDER BY Salary ASC;
you will get output,
SecHightestSalary
============
200

screenshot:

Hope this will help you.
0
Amit Kumar9y ago
hi,

select top(1) id from ( select top (2) id from Tab1 order by id desc) as oo order by id asc
0
Nilesh Patil9y ago
Hi,

This query find secon highest sallary of employee

select *
from Employee
where Salary In (select MAX(Salary) from Employee where Salary NOT IN (Select MAX(Salary) from employee));

0