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