How to find second highest or maximum

Question

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

Poonam Bhosale · Answer

select max(column_name) from table_name where column_name For example :- select max(Salary) from Employee where Salary

Nilofers Shaikh · Answer

thank u... :)

Tushar Dikshit · Answer

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/

Vighnesh Gadekar · Answer

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

Amit Arora · Answer

;with temp as ( Select ROW_NUMBER() Over(order by Salary ASC) as RowNum, Salary from Employee ) Select Salary from temp where RowNum = 2

Ravi Patel · Answer

Hi, SELECT TOP 1 salary FROM ( SELECT DISTINCT TOP 2 salary FROM employee ORDER BY salary DESC) a ORDER BY salary

Vighnesh Gadekar · Answer

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.

Amit Kumar · Answer

hi, select top(1) id from ( select top (2) id from Tab1 order by id desc) as oo order by id asc

Nilesh Patil · Answer

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));

How to find second highest or maximum

Answers (9)