Postgresql DENSE_RANK

create table sales 
as
with sales(artist,song, qty) as
(
 values
 ('beatles','yesterday',1000),
 ('beatles','something',1000),
 ('beatles','and i love her', 900),
 ('elvis', 'jailbreak rock', 800),
 ('nirvana','lithium', 600), 
 ('tomjones','sexbomb', 400)
)
select * from sales

Desired output:

 
 artist  |      song      | qty  
---------+----------------+------
 beatles | yesterday      | 1000
 beatles | something      | 1000
 elvis   | jailbreak rock |  800
 nirvana | lithium        |  600
(4 rows)

Using MAX:

with each_artist_top_selling_product as
(
        
 select artist, song, qty 
 from sales x  
 where qty = (select max(qty) from sales where artist = x.artist)
),
top_n_total as
(
select distinct qty from each_artist_top_selling_product order by qty desc limit 3
)
select * 
from each_artist_top_selling_product
where qty in (select qty from top_n_total)
order by qty desc

Using DENSE_RANK instead:

with each_artist_top_selling_product as
(
 select * from 
 (
  select artist, song, qty, dense_rank() over(partition by artist order by qty desc) rn
  from sales  
 ) as x where rn = 1
),
top_n_total as
(
select distinct qty from each_artist_top_selling_product order by qty desc limit 3
)
select * 
from each_artist_top_selling_product
where qty in (select qty from top_n_total)
order by qty desc