how to get the 3rd highest salary using sql server

narasiman rao

9y6k

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.

8 Replies

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

Jignesh Kumar4y ago
Hi,
you can achieve in different way, Please refer this link which will help you out,
https://www.c-sharpcorner.com/article/find-nth-highest-salary-in-sql-server-2017/
0
geetha geetha4y ago
SELECT TOP 1 salary
FROM
    (SELECT TOP 3 salary
     FROM salary
     ORDER BY salary DESC) AS Comp
ORDER BY salary ASC
Here I have a table named salary

0
Purvesh Sangani9y ago
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;
0
ashok maram9y ago
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)
+1
Tapan Patel9y ago
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
0
Guest User9y ago
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;
+1
Puneet Kankar9y ago
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
+2
Tapan Patel9y ago
1. SELECT SUBQ.name ,
2. SUBQ.salary
3. FROM (
4. SELECT
5. name ,
6. salary ,
7. RANK() OVER (ORDER BY salary DESC) [SalaryRank]
8. FROM #temptable
9. ) AS SUBQ
10. WHERE SUBQ.SalaryRank=3
0