How to get 3rd Highest Salary in Employee Table

Hi Sanwar

You may need to add DESC into the ORDER BY to produce the TOP 3

Nik

Hi. You need to use dense_rank() using cte and which will be very fast as compared to others. Use like this, ;WITH RESULT AS (SELECT SALARY,DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANKFROM EMPLOYEES ) SELECT TOP 1 SALARY FROM RESULT WHERE DENSERANK = 3 . If you want to find any other highest salary then replace the number with 3 . Thanks

Sanwar Ranwa7y ago

Hello

Please check these Links

create table emp(Ename varchar(50),sal int)
insert into emp values('A',1100)
insert into emp values('B',1500)
insert into emp values('C',4100)
insert into emp values('D',3100)
SELECT SAL,ENAME
FROM
(
SELECT SAL,ENAME,ROW_NUMBER() OVER(ORDER BY SAL) As RowNum
FROM EMP
) As A
WHERE A.RowNum IN (3)