CREATE TABLE EMP(EmpID INT,EmpName VARCHAR(10),Salary INT)
INSERT INTO EMP VALUES
(1,'Suresh',7000),
(2,'Prasanthi',8000),
(3,'Mahesh',9000),
(4,'Sai',10000),
(5,'Nagaraju',11000),
(6,'Mahendra',12000),
(7,'Sanjay',13000),
(8,'Santhosh',14000),
(9,'Raju',15000),
(10,'Phani',10000),
(11,'Kumar',12000),
(12,'Prasad',9000),
(13,'Siva',12000),
(14,'Madhav',14000),
(15,'Donthi',11000)
SELECT * FROM EMP ORDER BY SALARY
EmpID EmpName Salary
----------- ---------- -----------
1 Suresh 7000
2 Prasanthi 8000
3 Mahesh 9000
12 Prasad 9000
10 Phani 10000
4 Sai 10000
5 Nagaraju 11000
15 Donthi 11000
11 Kumar 12000
13 Siva 12000
6 Mahendra 12000
7 Sanjay 13000
8 Santhosh 14000
14 Madhav 14000
9 Raju 15000
--Find 2nd,3rd and 5th highest salary of employee in SQL Server
SELECT T1.*
FROM EMP T1
WHERE T1.EMPID IN (SELECT T1.EMPID
FROM EMP T2
WHERE T2.SALARY>=T1.SALARY
HAVING COUNT(DISTINCT SALARY) IN (2,3,5))
ORDER BY T1.SALARY DESC
--Result
EmpID EmpName Salary
----------- ---------- -----------
8 Santhosh 14000
14 Madhav 14000
7 Sanjay 13000
5 Nagaraju 11000
15 Donthi 11000
--Method can also be used find out the 'n'th highest Salaried employee
--Here I have explained how to find 5th highest Salaried employee
SELECT T1.*
FROM EMP T1
WHERE T1.EMPID IN (SELECT T1.EMPID
FROM EMP T2
WHERE T2.SALARY>=T1.SALARY
HAVING COUNT(DISTINCT SALARY)=4)
EmpID EmpName Salary
----------- ---------- -----------
6 Mahendra 12000
11 Kumar 12000
13 Siva 12000
Also Check Out Other Methods:
No comments:
Post a Comment