Tutorial :How to get top “N'th Maximum” value alone from a table in MS SQL?



Question:

Assume that there is a 'Employee' table with salary as one of the columns.

I want to get the 'Top N'th maximum salary alone from the table.

How this can be fetched easily?


Solution:1

SELECT TOP 1 employee.name, employee.salary from (      SELECT TOP N employee.name, employee.salary      FROM employee      ORDER BY employee.salary DESC )  

This gives the Nth from the top.


Solution:2

WITH    (          SELECT  e.*, ROW_NUMBER() OVER (ORDER BY employee.salary DESC) AS rn          FROM    employee          ) AS q  SELECT  *  FROM    q  WHERE   rn = @n  


Solution:3

SELECT TOP 1 E.Salary  FROM (SELECT TOP(N) Salary        FROM Employee        ORDER BY Salary DESC) E  ORDER BY E.Salary  


Solution:4

SELECT TOP N employee.name, employee.salary  FROM employee  ORDER BY employee.salary DESC  

?


Solution:5

SELECT TOP 1 Salary FROM  (SELECT TOP(N) Salary FROM Employee  ORDER BY Salary DESC) E  


Solution:6

 SELECT TOP 1 * FROM Employees      ORDER BY Employees.Salary DESC  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »