Find N-th highest salary from a table?

You can find n-th highest/lowest salary/value from a table using one of the following methods.

First, let’s create a sample table.

-- Table Schema
CREATE TABLE Players (
    name VARCHAR(50),
    salary DECIMAL(10,2)
);

-- Add some data
INSERT INTO Players (name, salary) VALUES
    ('Sakib Al Hasan', 50000.00),
    ('Tamim Iqbal', 60000.50),
    ('Mahmudullah Riyad', 60000.50),
    ('Mushfiqur Rahim', 75000.75),
    ('Mustafizur Rahman', 45000.25),
    ('Taskin Ahmed', 45100.25),
    ('Afif Hossain', 45200.25),
    ('Imrul Kayes', 21390.25),
    ('Saif Uddin', 53800.25),
    ('Ebadot Hossain', 80000.00);

-- Check the Data
SELECT * FROM Players;

Here’s how our table now looks like

Players Table

Using dense_rank()

Dense rank returns serial no based on values. For example- input [100, 80, 80, 70, 60, 60, 50] will return serial/rank [1,2,2,3,4,4,5] which is handy for ranking.

SELECT 
  name, 
  salary, 
  DENSE_RANK() OVER (ORDER BY salary DESC) AS sal_rank 
FROM 
  Players;

We now get a ranking of salary

Salary ranking

Now, we can return the n-th highest salary. In this example we find 5th highest.

SELECT 
  * 
FROM 
  (SELECT 
    name, 
    salary, 
    DENSE_RANK() OVER (ORDER BY salary DESC) AS sal_rank 
  FROM 
    Players) AS subquery 
WHERE 
  sal_rank = 3;

This gives us details of 3rd highest salaried player.

n-th highest salary

Finding N-1 higher salaries. For 3rd highest, we find salary — for which (3–1) = 2 higher salaries exists

SELECT 
  * 
FROM 
  Players e1 
WHERE 
  2 = (
    SELECT 
      COUNT(DISTINCT(salary)) 
    FROM 
      Players e2 
    WHERE 
      e2.salary > e1.salary
  );

Here’s the output.

n-th highest

This approach requires 3 steps

1. Find top N salaries
2. Find minimum of these N salaries
3. Find the persons with the salary value of step 2

Here’s the query

SELECT 
  * 
FROM 
  Players 
WHERE 
  salary = (
    SELECT 
      MIN(salary) 
    FROM 
      Players 
    WHERE 
      salary IN (
        SELECT 
          DISTINCT salary 
        FROM 
          Players 
        ORDER BY 
          salary DESC 
        LIMIT 3
        )
    );

And here’s the output.

Now you can try fetching n-th lowest salary as well.