Skip to content
Advertisement

Select 2 products per city with most counts in PostgreSQL

I have this dataset:

I want the top 2 counts per the_city, so the expected result should be:

I have tried this but it’s wrong

Advertisement

Answer

It’s greatest-n-per-group problem. You can use row_number()over() window function to to serialized city wise product list in descending order of count(*). Then select first two rows from each city.

Query:

Output:

the_city the_product product_count
EVORA D 4
EVORA B 2
LISBO A 5
LISBO B 2
PORTO C 3
PORTO B 2

db<fiddle here

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