How to Get the Top N Items for Each Group Using SQL's GROUP BY Clause?

Top N Items for Each Group Using SQL Group By

In a database, the GROUP BY clause is used to group rows in a table based on common values in one or more columns. When combined with aggregate functions like SUM(), it can be used to calculate statistics for each group. However, in some cases, it may be necessary to extract the top N items within each group.

One approach to this problem is to use a subquery to calculate the total sales for each item at each store and then rank them within each store. This can be achieved using the ROW_NUMBER() function to generate a ranking for each row, partitioned by store:

;WITH s AS 
(
  SELECT StoreID, UPCCode, tds, rn = ROW_NUMBER()
  OVER (PARTITION BY StoreID ORDER BY tds DESC)
  FROM 
  (
    SELECT StoreID, UPCCode, tds = SUM(TotalDollarSales)
    FROM Sales
    GROUP BY StoreID, UPCCode
  ) AS s2
)

The subquery creates a new table s with three columns: StoreID, UPCCode, and tds (total dollar sales). It also includes a ranking column rn, which gives each row a ranking within its store. The ranking is generated using the ROW_NUMBER() function and is partitioned by the StoreID column.

To extract the top 5 sold items for each store, the following query can be used:

SELECT StoreID, UPCCode, TotalDollarSales = tds
FROM s
WHERE rn <= 5
ORDER BY StoreID, TotalDollarSales DESC;

This query selects all rows from the s table where the rn value is less than or equal to 5. The results are sorted in descending order by TotalDollarSales within each store. As a result, the output will contain the top 5 sold items for each store in the Sales table. This method combines the use of GROUP BY, aggregate functions, and ranking to efficiently retrieve the requested data in a single query.

The above is the detailed content of How to Get the Top N Items for Each Group Using SQL's GROUP BY Clause?. For more information, please follow other related articles on the PHP Chinese website!