How to Fetch The Nth Highest Salary from a Table Without Using Top and Sub-Query

How to fetch the nth highest salary from a table without using TOP and sub-query?

Try a CTE - Common Table Expression:

WITH Salaries AS
(
    SELECT 
       SalaryAmount, ROW_NUMBER() OVER(ORDER BY SalaryAmount DESC) AS 'RowNum'
    FROM 
       dbo.SalaryTable
)
SELECT
  SalaryAmount
FROM
  Salaries
WHERE
   RowNum <= 5

This gets the top 5 salaries in descending order - you can play with the RowNumn value and basically retrieve any slice from the list of salaries.

There are other ranking functions available in SQL Server that can be used, too - e.g. there's NTILE which will split your results into n groups of equal size (as closely as possible), so you could e.g. create 10 groups like this:

WITH Salaries AS
(
    SELECT 
       SalaryAmount, NTILE(10) OVER(ORDER BY SalaryAmount DESC) AS 'NTile'
    FROM 
       dbo.SalaryTable
)
SELECT
  SalaryAmount
FROM
  Salaries
WHERE
   NTile = 1

This will split your salaries into 10 groups of equal size - and the one with NTile=1 is the "TOP 10%" group of salaries.

Second Highest Salary without subquery

Using limit and offset (to skip highest salary)
In MySQL

select * from table order by Salary desc limit 1 offset 1

In SQL server

select * from table order by Salary desc offset 1 rows fetch next 1 row only

You can try with another Database here

http://sqlfiddle.com/#!9/15c32/1/0

How to fetch the nth highest record from a table without using system function like TOP, MAX and Order by ?

Naive approach using > ALL and stacked cte:

-- highest
SELECT *
FROM tab t1
WHERE col > ALL (SELECT col
                 FROM tab t2
                 WHERE t1.col < t2.col);

-- 2nd highest
WITH c1 AS (
  SELECT *
  FROM tab t1
  WHERE col > ALL (SELECT col
                 FROM tab t2
                 WHERE t1.col < t2.col)
)
SELECT *
FROM tab t1
WHERE col < ALL (SELECT col FROM c1)
  AND col > ALL (SELECT col
                 FROM tab t2
                 WHERE t1.col < t2.col 
                   AND col < ALL(SELECT col FROM c1))

And then you could stack this kind of query multiple times.

db<>fiddle demo

In case of ties it will work same as DENSE_RANK:

db<>fiddle demo 2

SQL Subquery to Return Nth Highest Salary along with Name, City, etc

simply add what you want the subquery to return into the select clause to have the columns you want to be included in the result table. But also remember to include these columns on the outer query so they appear in your final result. like such:

Select TOP 3 result.salary, result.name, result.age, result.city
FROM
     (Select DISTINCT TOP 3 tblPerson.salary, tblPerson.name, 
      tblPerson.age,tblPerson.city
      FROM tblPerson
      Order By Salary DESC) result
ORDER BY Salary

your result table from your subquery looks like this:

salary  name       age  city
90000   Mr.derp    21   Minneapolis
80000   Ms.herp    52   St.Paul
70000   Mr.blah    89   Fakecity

It has 3 rows cause of the keyword TOP 3 and the columns as the result of tblPerson.salary, tblPerson.name, tblPerson.age,tblPerson.city in the select clause. Then we can simply select from this table with the outer query.

You can use select * if you want every columns or specify whichever column you want with the column name. You can change n in TOP n to see how many rows you want to select from the top.

nth highest salary using subquery and dense_rank doesn't match when table has duplicate salaries

DENSE_RANK() does not do what you want. It assigns incremental numbers while giving the same rank to ties. So there could be more than 6 records with a higher salary than the records ranked 7th.

The other query gives you the correct result, but could be simplified by using the OFFSET/FETCH syntax, which is available in SQL Server since version 2012:

select * from employee order by salary offset 6 rows fetch next 1 row only

---

Related Topics