How to Query 1st, 2nd, or Nth Largest attribute in SQL Table

In SQL, selecting the first, second, or nth largest attribute from any table is a common requirement. In this article we will go through the steps to achieve this using SQL queries.
Let's do it with an example, assume we have an employee table and a department table, with the employee table containing salary information.

Prerequisites:
Before proceeding, make sure you have a working knowledge of SQL and have access to a database management system (DBMS) such as MySQL, PostgreSQL, or Oracle.

Step 1: Understand the Schema
To begin, let's assume we have two tables: employee and department. The employee table contains columns such as employee_id, name, salary, and department_id. The department table contains columns like department_id and department_name.

Step 2: Select 1st Largest Salary
To select the largest salary from the employee table, you can use the following SQL query:

SELECT MAX(salary) AS largest_salary
FROM employee

This is pretty much simple query, and we get the largest salary easily.

Step 3: Select 2nd Largest Salary
To select the second-largest salary, you can modify the previous query by adding a condition:

SELECT MAX(salary) AS second_largest_salary
FROM employee
WHERE salary < (
SELECT MAX(salary)
FROM employee
)

This query uses a subquery to find the maximum salary and then selects the maximum salary that is less than the maximum salary found in the subquery.

Step 4: Select Nth Largest Salary
To select the Nth largest salary, you can generalize the previous approach by introducing a variable or parameter:

SET @n = 4; -- Specify the desired Nth largest value

SELECT MAX(salary) AS nth_largest_salary
FROM employee
WHERE salary < (
  SELECT MAX(salary)
  FROM employee
  WHERE salary < (
    SELECT MAX(salary)
    FROM employee
    WHERE salary < (
      SELECT MAX(salary)
      FROM employee
      WHERE salary < (
        -- Continue this pattern until N - 1 subqueries are added
        SELECT MAX(salary)
        FROM employee
      )
    )
  )
)

In this example, we use a variable @n to represent the desired Nth largest value. You can adjust this variable as needed.

Is it Horrible to query 8th largest salary?

Definitely, Not. We have OFFSET and FETCH.

Step 1: Select 2nd Largest Salary
To select the second-largest salary from the "employee" table in PostgreSQL, you can use the following SQL query:

SELECT salary
FROM employee
ORDER BY salary DESC
OFFSET 1
FETCH FIRST 1 ROW ONLY;

This query orders the salaries in descending order and skips the first row (highest salary) using the OFFSET clause. Then, it fetches the first row only, giving us the second-largest salary.

Step 2: Select 3rd Largest Salary
To select the third-largest salary, you can modify the previous query by changing the OFFSET value:

SELECT salary
FROM employee
ORDER BY salary DESC
OFFSET 2
FETCH FIRST 1 ROW ONLY;

Here, we set the OFFSET to 2, skipping the first two rows (two highest salaries), and fetching the first row, which corresponds to the third-largest salary.

Step 3: Select Nth Largest Salary
To select the Nth largest salary, you can generalize the previous approach by introducing a variable or parameter:

DECLARE @n INT = 4; -- Specify the desired Nth largest value

SELECT salary
FROM employee
ORDER BY salary DESC
OFFSET (@n - 1)
FETCH FIRST 1 ROW ONLY;

In this example, we introduce a variable @n to represent the desired Nth largest value. You can adjust this variable as needed.

Conclusion:
Using the OFFSET and FETCH clauses in PostgreSQL allows for an efficient and concise way to select the second, third, and Nth largest salaries from an employee table.