Monday 19 May 2014

select top 3 rd or nth highest salary from emp table using sql query without using top keyword

Query 1->>  Using   DENSE_RANK()     *******************************************

SELECT Salary,username
FROM
(
SELECT Salary,username,DENSE_RANK() OVER(ORDER BY Salary DESC) Rno from Emp
) tbl
WHERE Rno=nth


Query 2->>  Using   ROW_NUMBER()     *******************************************

SELECT Salary,username
FROM
(
SELECT Salary,username,ROW_NUMBER() OVER(ORDER BY Salary DESC) Rno from Emp
) tbl
WHERE Rno=nth

Query 3->>  Using  top keyword     *******************************************
SELECT TOP 1 salary
FROM (
      SELECT DISTINCT TOP 3 salary
      FROM Emp
      ORDER BY salary DESC
      ) a
ORDER BY salary

Query 3->>  Using  Sub Query   *******************************************
SELECT *
FROM Emp Emp1
WHERE (3-1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Emp Emp2

WHERE Emp2.Salary > Emp1.Salary)


No comments:

Post a Comment

Excel Sort values in ascending order using function TEXTJOIN

 Excel ::  Text ::  1,3,5,2,9,5,11 Result :: 1,2,3,5,5,9,11 Formula ::     TEXTJOIN ( ",",1,SORT(MID(SUBSTITUTE( A1 ,","...