DEV Community

Muhammad Muneeb Ur Rehman
Muhammad Muneeb Ur Rehman

Posted on

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
      )
    )
  )
)
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)