How to select TOP records in Postgres. Not a similar question

I have a table with three columns (City, Orderid, Total_quantity) and more than 500 rows. I want to fetch the top 50 records according to total_quantity wise. I can do this with rank_number but the twist can be understood with an example.

Example: Select top 5 (only for example) considering there are only 3 cities.

Table1
City Orderid Total_quantity
---------------------------
A      1      90
A      2      80
A      3      70     
B      4      70
B      5      65
B      6      55
C      7      79    
C      8      24

'
'
'

If I select the top 5 then the first record should be from city A with max total Quantity, the second record should be from city B with max total quantity, the third record should be from city C with max quantity. Here is the twist, the fourth record should be from city A with max quantity and then the final fifth record should be from city B with max total quantity. So the output will be like

Output:

Table1
    City Orderid Total_quantity
    ---------------------------
    A      1      90
    B      4      70
    C      7      79
    A      2      80   
    B      5      65
    '
    '
    '

Answer

select City, Orderid, total_quantity,
       rank() OVER (PARTITION BY City ORDER BY total_quantity desc) as rank_quantity
 from table
 order by rank_quantity,city LIMIT 5;

Let me know if it works

Advertisement

Answer