This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check below input/output data, and the employee third highest employee salary.
Input data:
|
1 2 3 4 5 6 7 8 9 10 |
empid ename salary ----------- ---------- ----------- 1 pqr 25000 2 abc 25000 3 xyz 37000 4 pqr 35200 5 ymf 40000 6 sfs 40000 7 wer 37000 8 cvb 37800 |
Require output:
|
1 2 3 4 |
empid ename salary ----------- ---------- ----------- 3 xyz 37000 7 wer 37000 |
Create a table with sample data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
create table tbl_employee ( empid int primary key ,ename varchar(10) ,salary int ) GO insert into tbl_employee values (1,'pqr',25000) ,(2,'abc',25000) ,(3,'xyz',37000) ,(4,'pqr',35200) ,(5,'ymf',40000) ,(6,'sfs',40000) ,(7,'wer',37000) ,(8,'cvb',37800) GO |
Solution 1 (Correlated Subquery):
|
1 2 |
select * from tbl_employee as a where 3 = (select count(distinct salary) from tbl_employee as b where a.salary<=b.salary) |
Solution 2 (DENSE_RANK()):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
with cteEmp as ( select empid ,ename ,salary ,DENSE_RANK() over (order by salary desc) as rnk from tbl_employee ) select empid ,ename ,salary from cteEmp where rnk = 3 |
Solution 3 (MAX() & CROSS APPLY):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT a.ename, b.salary FROM ( SELECT MAX(salary) Salary FROM tbl_employee d WHERE d.Salary != ( SELECT MAX(salary) Salary FROM tbl_employee e WHERE e.Salary != (SELECT MAX(salary) FROM tbl_employee) ) and d.Salary != (SELECT MAX(salary) FROM tbl_employee) ) b CROSS APPLY (SELECT ename FROM tbl_employee WHERE salary = b.salary ) a |
Leave a Reply