Selecting the Bottom N Rows in SQL Queries

1. Overview

In SQL, retrieving a subset of data from a larger dataset is a common task. In particular, there might be a need to focus on the most recent, top-performing, or least relevant records.

Selecting the top N rows is a straightforward process in most SQL dialects. Selecting the bottom N rows can be more nuanced and may require different approaches depending on the database being used.

In this tutorial, we’ll explore various methods for selecting the bottom N rows in SQL across different databases, focusing on PostgreSQL, MySQL, and SQL Server. We’ll use various tables from the Baeldung University database to illustrate each method.

2. Implementation in PostgreSQL

PostgreSQL provides multiple ways to retrieve the bottom N rows from a dataset. Furthermore, each method has its strengths and may be more suitable depending on the specific use case and the complexity of the query.

2.1. Using the ORDER BY and LIMIT Clauses

The ORDER BY and LIMIT clauses offer a straightforward approach to selecting the bottom N rows. We sort the records in ascending or descending order and limit the number of rows returned.

For example, let’s use the query to retrieve the bottom 5 students with the lowest GPA using the Student table:

SELECT id, name, gpa
FROM Student
ORDER BY gpa ASC
LIMIT 5;
+------+---------------+------+
| id   | name          | gpa  |
|------+---------------+------|
| 1717 | Param Mohan   | 2.75 |
| 1621 | Vineet Jha    | 2.9  |
| 1707 | Piu Liu       | 2.99 |
| 2008 | Julia Roberts | 3.04 |
| 1111 | Vikram Kohli  | 3.27 |
+------+---------------+------+
SELECT 5
Time: 0.009s

In the query, we use ORDER BY gpa ASC to sort the students by GPA in ascending order, placing the students with the lowest GPAs at the top. Additionally, the LIMIT 5 clause restricts the result set to the bottom 5 students.

This method is efficient and works seamlessly across PostgreSQL and MySQL, making it a go-to choice for this type of query.

2.2. Using the ROW_NUMBER() Window Function

The ROW_NUMBER() window function assigns a unique row number to each record based on a specified ordering. In particular, it’s useful when more complex queries are needed, or when the result needs to be further processed.

For example, let’s use ROW_NUMBER() to select the bottom 5 students with the lowest GPA:

WITH RankedStudents AS (
    SELECT id, name, gpa,
        ROW_NUMBER() OVER (ORDER BY gpa ASC) AS row_num
    FROM Student
)
SELECT id, name, gpa
FROM RankedStudents
WHERE row_num <= 5;
+------+---------------+------+
| id   | name          | gpa  |
|------+---------------+------|
| 1717 | Param Mohan   | 2.75 |
| 1621 | Vineet Jha    | 2.9  |
| 1707 | Piu Liu       | 2.99 |
| 2008 | Julia Roberts | 3.04 |
| 1111 | Vikram Kohli  | 3.27 |
+------+---------------+------+
SELECT 5
Time: 0.010s

Here, we use the ROW_NUMBER() function to assign a rank to each student based on their GPA in ascending order. Furthermore, the WITH clause creates a common table expression (CTE) named RankedStudents, which stores these ranked results. Finally, the outer query then filters the result set to include only the bottom 5 students.

This method is versatile and can be adapted for more complex queries, such as handling ties or applying additional filters. Additionally, it’s also supported by MySQL, offering a consistent approach across both databases.

3. Implementation in MySQL

MySQL, like PostgreSQL, offers multiple ways to retrieve the bottom N rows from a dataset. We can use subqueries combined with the ORDER BY clause to select the bottom N rows in MySQL.

For example, let’s retrieve the bottom 5 students with the lowest GPA, excluding those with NULL GPAs:

SELECT id, name, gpa
FROM (
    SELECT id, name, gpa
    FROM Student
    WHERE gpa IS NOT NULL
    ORDER BY gpa ASC
    LIMIT 5
) AS BottomStudents
ORDER BY gpa ASC;
+------+-------------+------+
| id   | name        | gpa  |
+------+-------------+------+
| 1717 | Param Mohan | 2.75 |
| 1210 | Jason Bourne| 2.80 |
| 1811 | Kim Yi      | 2.85 |
| 1910 | Ana Patel   | 2.90 |
| 1613 | Ravi Kumar  | 2.92 |
+------+-------------+------+
5 rows in set (0.004 sec)

In this query, the inner subquery retrieves the bottom 5 students based on their GPA, excluding any rows where the GPA is NULL. The outer query then takes this result set and applies an additional ORDER BY gpa ASC to ensure the final output is ordered by GPA in ascending order.

This method provides flexibility, allowing further operations on the result set before returning the final output. Additionally, it’s efficient for scenarios where you need to retrieve and process a subset of the data before making the final selection.

4. Implementation in SQL Server

SQL Server offers several methods to retrieve the bottom N rows from a dataset. These methods leverage SQL Server’s unique functionalities, ensuring efficient and accurate results.

4.1. Using the ORDER BY and OFFSET-FETCH Clauses

We use the OFFSET-FETCH clause in SQL Server to skip a specified number of rows and then retrieve the next set of rows. In particular, it’s useful for pagination and retrieving specific subsets of data.

For example, let’s select the bottom 5 students with the lowest GPA:

SELECT id, name, gpa
FROM Student
WHERE gpa IS NOT NULL
ORDER BY gpa ASC
OFFSET (SELECT COUNT(*) FROM Student WHERE gpa IS NOT NULL) - 5 ROWS
FETCH NEXT 5 ROWS ONLY;
+------+-----------------+------+
| id   | name            | gpa  |
+------+-----------------+------+
| 1717 | Param Mohan     | 2.75 |
| 1621 | Vineet Jha      | 2.90 |
| 1707 | Piu Liu         | 2.99 |
| 2008 | Julia Roberts   | 3.04 |
| 1011 | Vikas Jain      | 3.30 |
+------+-----------------+------+
SELECT 5
Time: 0.005s

In this query, we use the WHERE gpa IS NOT NULL to exclude records where GPA is NULL to ensure accurate ordering. Then, the ORDER BY gpa ASC sorts the students by GPA in ascending order, placing the lowest GPAs first.

Additionally, the OFFSET calculates the number of rows to skip by subtracting 5 from the total count of non-NULL GPA records. Finally, the FETCH query retrieves the next 5 rows after the specified offset. This effectively selects the bottom 5 students with the lowest GPAs.

4.2. Using the ROW_NUMBER() Window Function

We can also use the ROW_NUMBER() function in SQL Server to select the bottom N records. This method is beneficial for more complex queries and provides greater flexibility, such as handling ties or applying additional filters.

For example, let’s select the bottom 5 students with the lowest GPA:

WITH RankedStudents AS (
    SELECT id, name, gpa,
        ROW_NUMBER() OVER (ORDER BY gpa ASC) AS row_num
    FROM Student
    WHERE gpa IS NOT NULL
)
SELECT id, name, gpa
FROM RankedStudents
WHERE row_num <= 5
ORDER BY gpa ASC;
+------+---------------+------+
| id   | name          | gpa  |
+------+---------------+------+
| 1717 | Param Mohan   | 2.75 |
| 1621 | Vineet Jha    | 2.90 |
| 1707 | Piu Liu       | 2.99 |
| 2008 | Julia Roberts | 3.04 |
| 1011 | Vikas Jain    | 3.30 |
+------+---------------+------+
SELECT 5
Time: 0.006s

Here, we use the ROW_NUMBER() function to generate sequential row numbers starting from 1 for the lowest GPA. Also, the WHERE row_num <= 5 filters the CTE to include only the top 5 rows, which correspond to the bottom 5 students with the lowest GPAs. Finally, the ORDER BY gpa ASC ensures the final output is ordered by GPA in ascending order.

5. Conclusion

In this article, we’ve explored how to select the bottom N rows in PostgreSQL, MySQL, and SQL Server. Each database offers its unique strengths, allowing for efficient and flexible data retrieval based on specific criteria.

Understanding these methods helps handle diverse data scenarios effectively across different SQL environments.