How to find the top n quantities within a table-CodePudding

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