How to find multiple employee(s) having 'n'th highest salary (when more than one employee are having same salary)

CREATE TABLE EMP_SALARY(EMP_ID INT,SALARY INT)

INSERT INTO EMP_SALARY VALUES(1,10000),(2,5000),(3,6000),(4,6000),(5,5000),(6,6000)

SELECT * FROM EMP_SALARY

--Output:

EMP_ID      SALARY

----------- -----------

1           10000

2           5000

3           6000

4           6000

5           5000

6           6000

--1st method using 'WITH TIES' along with 'TOP'

SELECT TOP 1 WITH TIES A.*

FROM EMP_SALARY A

WHERE A.EMP_ID IN ( SELECT TOP 2 WITH TIES B.EMP_ID

                    FROM EMP_SALARY B

                    ORDER BY B.SALARY DESC)

ORDER BY A.SALARY

--Result:

EMP_ID      SALARY

----------- -----------

3           6000

4           6000

6           6000

--2nd method using 'COUNT'

SELECT A.*

FROM EMP_SALARY A

WHERE 2=(SELECT COUNT(DISTINCT B.SALARY)

         FROM EMP_SALARY B

         WHERE B.SALARY>=A.SALARY)

--Result:

EMP_ID      SALARY

----------- -----------

3           6000

4           6000

6           6000

--3rd method using 'DENSE_RANK'

SELECT A.EMP_ID,A.SALARY

FROM (SELECT EMP_ID,SALARY,

             DENSE_RANK() OVER(ORDER BY SALARY DESC) AS SALARY_ORD

      FROM EMP_SALARY)A

WHERE A.SALARY_ORD=2

--Result:

EMP_ID      SALARY

----------- -----------

3           6000

4           6000

6           6000

                        How to find employee having 'n'th highest Salary>>

How to find employee having 'n'th highest Salary

/* I have explained a scenario where we want to know emplyee having 2nd highest salary */

CREATE TABLE EMP_SALARY(EMP_ID INT,SALARY INT)

INSERT INTO EMP_SALARY VALUES(1,10000),(2,5000),(3,6000)

SELECT * FROM EMP_SALARY

--Output:

EMP_ID      SALARY

----------- -----------

1           10000

2           5000

3           6000

--1st method using 'TOP'

SELECT TOP 1 A.*

FROM EMP_SALARY A

WHERE A.EMP_ID IN ( SELECT TOP 2 B.EMP_ID

                    FROM EMP_SALARY B

                    ORDER BY B.SALARY DESC)

ORDER BY A.SALARY

--Result:

EMP_ID      SALARY

----------- -----------

3           6000

--2nd method using 'COUNT'

SELECT A.*

FROM EMP_SALARY A

WHERE 2=(SELECT COUNT(B.SALARY)

         FROM EMP_SALARY B

         WHERE B.SALARY>=A.SALARY)

--Result:

EMP_ID      SALARY

----------- -----------

3           6000

--3rd method using 'DENSE_RANK'

SELECT A.EMP_ID,A.SALARY

FROM (SELECT EMP_ID,SALARY,

             DENSE_RANK() OVER(ORDER BY SALARY DESC) AS SALARY_ORD

      FROM EMP_SALARY)A

WHERE A.SALARY_ORD=2

--Result:

EMP_ID      SALARY

----------- -----------

3           6000

      when more than one employee are having same salary >>