Skip to content
Advertisement

How to find the three greatest values in each category in PostgreSQL?

I am a SQL beginner. I have trouble on how to find the top 3 max values in each category. The question was

“For order_ids in January 2006, what were the top (by revenue) 3 product_ids for each category_id? “

I tried to combine table B and A using an Inner Join and filtered by the order_date. But then I am stuck on how to find the top 3 max values in each category_id. Thanks.

This is so far what I can think of

Advertisement

Answer

This kind of query is typically solved using window functions

dense_rank() will also deal with ties (products with the same revenue in the same category) so you might actually get more than 3 rows per product/category.

If the same product can show up more than once in table b (for the same category) you need to combine this with a GROUP BY to get the sum of all revenues:

When combining window functions and GROUP BY, the window function will be applied after the GROUP BY.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement