SQL Server

Real answer for How to find second highest salary in SQL Server

There a Table EMP_ID, EMP_NAME, and EMP_SALARY of Employee, get names of the second highest salary Employee, in SQL Server


(1)First of all create EMPLOYEE Table in SQL Server Like this

CREATE TABLE EMPLOYEE
(
EMP_ID INT IDENTITY(1,1),
EMP_NAME VARCHAR(100),
EMP_SALARY INT
)

(2)Then After Insert Record in EMPLOYEE Table

INSERT INTO Employee VALUES('TEST1',50000)
INSERT INTO Employee VALUES('TEST2',45000)
INSERT INTO Employee VALUES('TEST3',55000)
INSERT INTO Employee VALUES('TEST4',50000)
INSERT INTO Employee VALUES('TEST5',40000)

Output like this




 (3) Find  second highest salary Employee, in SQL Server


SELECT
DENSE_RANK() OVER(ORDER BY EMP_SALARY DESC) AS SRNO,
EMP_NAME,
EMP_SALARY
FROM Employee
ORDER BY EMP_SALARY DESC


After Run This query

SELECT * FROM (
SELECT
DENSE_RANK() OVER(ORDER BY EMP_SALARY DESC) AS SRNO,
EMP_NAME,
EMP_SALARY
FROM Employee
) AS EMP
WHERE SRNO=2

Your Answer like this







1 comment: