How to use a Subquery In SQL

Let’s see How to retrieve the top 2 rows per product_id based on the amount in descending order from a table called “sales”

-- Method 2: Using a Subquery
SELECT 
    id, 
    product_id, 
    amount
FROM (
    SELECT 
        id, 
        product_id, 
        amount,
        ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY amount DESC) AS row_num
    FROM 
        sales
) AS ranked_sales
WHERE 
    row_num <= 2;

Output:

Explanation:

• This approach is comparable to Method 1 but substitutes a subquery for the CTE.
• We build a subquery that applies Method 1’s logic: arranging the rows in each group according to decreasing order based on the “amount” column.
• The top two rows per “product_id” are then retrieved by the outer query by choosing rows from the subquery where the row number is less than or equal to 2.

In data analysis, understanding how to extract meaningful insights from grouped data is crucial. PostgreSQL, a powerful open–source relational database management system, provides robust features to handle such scenarios effectively.

One common analytical task is limiting results to the top N rows within each group, which can be invaluable for various analyses, such as tracking student performance, analyzing sales data, or evaluating product popularity.

In this article, we’ll explore three effective methods in PostgreSQL to restrict results to the top N rows per group, allowing you to make the most of your data analysis efforts.