Running a Query With LIMIT/OFFSET While Still Getting the Total Row Count

1. Introduction

As a Data Analyst, obtaining the total row count alongside paginated results is often essential for our business needs.

In this tutorial, we’ll utilize LIMIT and OFFSET clauses to retrieve specific data subsets with the window functions, specifically COUNT(*) OVER(), to calculate the total row count alongside the paginated results efficiently.

Also, we’ll explore practical example use cases for this scenario using the Baeldung University database schema and sample data.

2. Illustrative Data Sample

Let’s refer to the sample Faculty table, which includes faculty members detailing their roles and employment statuses:

> SELECT * FROM Faculty;
 id  |      name      | national_id |      position       | start_date |  end_date  | department_id | active
-----+----------------+-------------+---------------------+------------+------------+---------------+--------
   1 | Anubha Gupta   |  1018901231 | Professor           | 2010-01-11 | 2027-03-11 |             2 | t
 111 | AV Subramanium |  1340902317 | Assistant Professor | 2011-05-11 |            |             1 | t
 121 | Risa Sodi      |  1409239017 | Associate Professor | 2010-01-11 |            |             1 | t
 512 | Casper Jones   |  4253513301 | Teaching Assistant  | 2021-04-11 |            |             3 | t
 601 | Sussie Smith   |  1657230918 | Professor           | 2019-01-11 | 2027-02-18 |             5 | t
 740 | Kira Wass      |  2314623876 | Teaching Assistant  | 2021-09-11 |            |             4 | t
 741 | Sophia Ker     |  2314437876 | Teaching Assistant  | 2022-08-11 |            |             4 | t
(7 rows)

In this context, the SELECT query enables us to display columns containing faculty details such as id, name, national_id, position, and more.

3. Understanding LIMIT and OFFSET

Generally, the LIMIT controls the maximum number of rows returned in a query, while OFFSET determines where to start from. For instance, the query with OFFSET 1 LIMIT 3 retrieves three rows from the Faculty table starting from the second row (due to OFFSET 1):

> SELECT * FROM Faculty ORDER BY id OFFSET 1 LIMIT 3;
 id  |      name      | national_id |      position       | start_date | end_date | department_id | active
-----+----------------+-------------+---------------------+------------+----------+---------------+--------
 111 | AV Subramanium |  1340902317 | Assistant Professor | 2011-05-11 |          |             1 | t
 121 | Risa Sodi      |  1409239017 | Associate Professor | 2010-01-11 |          |             1 | t
 512 | Casper Jones   |  4253513301 | Teaching Assistant  | 2021-04-11 |          |             3 | t
(3 rows)

Usually, this combination is useful for pagination, ensuring efficient retrieval of specific subsets of data based on sorting criteria like id.

Here, this query fetches 3 faculty names starting from the 2nd faculty, sorted by id. However, if we also need to show the total number of faculties, things become more complicated. We might then consider querying the total count of rows before applying LIMIT and OFFSET:

> SELECT COUNT(*) AS full_count FROM Faculty;
  full_count 
------------
          7
(1 row)

This query provides the total number of faculties in the Faculty table. However, when we introduce LIMIT and OFFSET to paginate the results, we lose the context of how many total rows would have been returned without these constraints.

4. Getting the Total Row Count

Now, let’s retrieve the total row count from the Faculty table using the COUNT(*) OVER() window function. This function computes the total count over the entire result set without grouping, offering a snapshot of the total row count alongside individual row data:

> SELECT id, name, position, start_date, end_date, department_id, COUNT(*) OVER() AS full_count FROM Faculty;
 id  |      name      |      position       | start_date |  end_date  | department_id | full_count
-----+----------------+---------------------+------------+------------+---------------+------------
   1 | Anubha Gupta   | Professor           | 2010-01-11 | 2027-03-11 |             2 |          7
 111 | AV Subramanium | Assistant Professor | 2011-05-11 |            |             1 |          7
 121 | Risa Sodi      | Associate Professor | 2010-01-11 |            |             1 |          7
 512 | Casper Jones   | Teaching Assistant  | 2021-04-11 |            |             3 |          7
 601 | Sussie Smith   | Professor           | 2019-01-11 | 2027-02-18 |             5 |          7
 740 | Kira Wass      | Teaching Assistant  | 2021-09-11 |            |             4 |          7
 741 | Sophia Ker     | Teaching Assistant  | 2022-08-11 |            |             4 |          7
(7 rows)

This query retrieves all columns from the Faculty table and includes an additional column full_count that displays the total number of rows in the Faculty table alongside each row’s data. Here, this method combines detailed row-level information with the overall count, where we need both detailed data and the total row count without running a separate query.

Now, let’s use this window function with OFFSET and LIMIT to retrieve rows from the Faculty table. Initially, it sorts by id, skipping the first row OFFSET 1, and then fetches the next 4 rows LIMIT 4. Additionally, the COUNT(*) OVER() window function computes the total row count of the entire Faculty table, providing this count alongside each row’s data:

> SELECT id, name, position, start_date, end_date, department_id, COUNT(*) OVER() AS full_count FROM Faculty ORDER BY id OFFSET 1 LIMIT 4;
 id  |      name      |      position       | start_date |  end_date  | department_id | full_count
-----+----------------+---------------------+------------+------------+---------------+------------
 111 | AV Subramanium | Assistant Professor | 2011-05-11 |            |             1 |          7
 121 | Risa Sodi      | Associate Professor | 2010-01-11 |            |             1 |          7
 512 | Casper Jones   | Teaching Assistant  | 2021-04-11 |            |             3 |          7
 601 | Sussie Smith   | Professor           | 2019-01-11 | 2027-02-18 |             5 |          7
(4 rows)

Technically, OFFSET skips the specified number of rows before beginning to return rows, which is crucial for pagination. LIMIT then restricts the number of rows returned after applying the offset. In this query, COUNT(*) OVER() independently calculates the total row count of the Faculty table, regardless of the pagination constraints imposed by OFFSET and LIMIT.

This method efficiently combines detailed row-level data with a total count in scenarios where pagination is needed but requires insight into the entire dataset’s size.

5. Conclusion

In summary, using the COUNT(*) OVER() window function along with OFFSET and LIMIT helps in paginating results while also obtaining the total row count of the dataset.

Also, this solution only functions when it returns rows. For instance, if OFFSET exceeds the number of rows that would otherwise be returned, the result set will be empty.