How to Restrict Results to Top N Rows per Group?

When working with large datasets, it’s often necessary to extract the top N rows per group based on certain criteria. SQLite provides several methods to achieve this, including the use of subqueries and window functions. Below are the methods that help us to extract the Top N Rows per Group in SQLite.

1. Using Subquery with Row Number
2. Using Correlated Subquery
   
3. Using Common Table Expression (CTE) with Window Function

Let’s Set up an ENVIRONMENT

To understand How to Restrict results to top N rows per group in SQLite we need a table on which we will perform various operations and queries. Here we will consider a table called sales_data which contains region, product, and revenue as Columns.

CREATE TABLE sales_data (
region TEXT,
product TEXT,
revenue REAL
);

INSERT INTO sales_data (region, product, revenue) VALUES
('North', 'Product A', 1000),
('North', 'Product B', 1500),
('North', 'Product C', 1200),
('South', 'Product A', 800),
('South', 'Product B', 1100),
('South', 'Product C', 900),
('East', 'Product A', 1200),
('East', 'Product B', 1000),
('East', 'Product C', 1300),
('West', 'Product A', 900);

Output:

Assume a situation where the data to be retrieved is grouped by specific criteria and the rows are desired to be filtered so that data from the top N rows in each group can be obtained, SQLite databases will be the tools used.

This can be particularly important where, for example, items need to be ranked categorically or to identify the top performers in different groups. While database systems that offer specialized functions for this task exist, SQLite doesn’t provide such core capabilities.

In this article, We will learn about How to Restrict results to the top N rows per group in SQLite by understanding various methods along with the examples and so on.