How to Query 1st, 2nd, or Nth Largest attribute in SQL Table
Tags: sql, postgres, backend, webdev
Published: 2023-05-20 10:05:47

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.