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

How to highlight selected text in notepad++

  –> To highlight a block of code in Notepad++, please do the following steps step-1  :- Select the required text. step-2  :- Right click...