how to get the 3rd highest salary using sql server

Question

Employee table as follows Name Salary Ram 12000 Asif 15000 Suresh 15000 mohan 18000 Bhuva 19000 asif 25500 Pavan 34000 i want to get the third highest salary using the above Employee table.

Puneet Kankar · Answer

try this Query 1 - SELECT TOP 1 salary FROM (SELECT TOP 3 salary FROM employees ORDER BY salary Query 2 - SELECT a.Name,a.sal FROM(SELECT Name,sal,DENSE_RANK() over(ORDER BY sal) AS rk FROM employee) as a where rk=3

Tapan Patel · Answer

Learning from Puneet's answer, you should go with query 1 (avoiding Rank function in select statement). But just one correction. Try doing distinct on salary becuase some employees may have same salary amount. SELECT TOP 1 CE.Salary FROM ( SELECT DISTINCT TOP 3 Salary FROM #temptable ORDER BY Salary DESC ) AS CE ORDER BY CE.Salary

Jothish Kumar · Answer

Just create a table and use the below codes to get your answers. Because I have got my answer for 12th highest salary for my company. SELECT * FROM ( SELECT Salary,rownum AS roworderFROM ( select distinct Salary from employer) ORDER BY Salary) where roworder = 3;

Tapan Patel · Answer

SELECT SUBQ. name , SUBQ.salary FROM ( SELECT name , salary , RANK() OVER ( ORDER BY salary DESC ) [SalaryRank] FROM #temptable ) AS SUBQ WHERE SUBQ.SalaryRank=3

Purvesh Sangani · Answer

You can get the third highest salary by using limit , by using TOP keyword and sub-query 1. Top Keyword : SELECT TOP 1 salary FROM (SELECT TOP 3 salary FROM Table_Name ORDER BY salary DESC) AS Comp ORDER BY salary ASC 2. limit Keyword : SELECT salary FROM Table_Name ORDER BY salary DESC LIMIT 2, 1 3. by subquery : SELECT salary FROM (SELECT salary FROM Table_Name ORDER BY salary DESC LIMIT 3) AS Comp ORDER BY salary LIMIT 1;

ashok maram · Answer

select top 1 salary from employee0 where salary in (select distinct top 3 Salary from employee0 order by Salary desc) order by salary asc (OR) select MIN(salary) from employee0 where salary in (select distinct top 3 Salary from employee0 order by Salary desc)

how to get the 3rd highest salary using sql server

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (6)