Home > other >  How to find the top n quantities within a table
How to find the top n quantities within a table

Time:08-26

Suppose in the table mentioned below, I wanted to select the top 3 quantities and if there are duplicates, I want them too and the required table is mentioned below too.

For this requirement, I tried something like the SQL below.

select name, quantity
from employee
order by name,quantity DESC
FETCH FIRST 3 ROWS WITH TIES;

But I'm not able to get any meaningful output. BTW I'm using postgres.

All I need is employee info along with quantities with 3 max quantities along with ties if any.

NAME | Quantity
---------
AAA     500
AAA     500
AAA     400
AAA     300
AAA     200
AAA     100


Required Table:

NAME | Quantity
--------------
AAA     500
AAA     500
AAA     400
AAA     300
```

CodePudding user response:

This can be achieved by using dense_rank.

select "NAME"
      ,"Quantity"
from  (
       select *
             ,dense_rank() over(order by "Quantity" desc) as dr
       from t
       ) t
where  dr <= 3 
NAME Quantity
AAA 500
AAA 500
AAA 400
AAA 300

Fiddle

  • Related