CODEWITHSUNDEEP
mysql
Gops61
Gops61

Reputation: 3

Find top 3 with ties in SQL for each day

If I have the following table,

Day Name Amount
Monday John 100
Monday Liam 120
Monday Rico 125
Monday Erin 110
Tuesday Maya 150
Tuesday John 150
Tuesday Liam 100
Tuesday Sern 120
Tuesday Rico 110
Wednesday Maya 500

For each day, I want to know who had the highest 3 amounts and what those amounts are. But if there are more than 3 people with highest 3 amounts then there can be more than 3 people for that day. If there are less 3 three people for a day then just report those. So the output should be,

Day Name Amount
Monday Rico 125
Monday Liam 120
Monday Erin 110
Tuesday Maya 150
Tuesday John 150
Tuesday Sern 120
Tuesday Rico 110
Wednesday Maya 500

Upvotes: 0

Views: 170

Answers (2)

FanoFN
FanoFN

Reputation: 7124

If you're using MySQL that supports window function, you can use DENSE_RANK():

SELECT dayname, names, amount
FROM
(SELECT *, 
       DENSE_RANK() OVER(PARTITION BY dayname ORDER BY amount DESC) AS rnk
FROM mytable) v
WHERE rnk <= 3
ORDER BY dayname, amount DESC;

If you're on older MySQL version, then you might try this:

SELECT mytable.*
 FROM mytable
JOIN
 (SELECT *,
       CASE WHEN @d = dayname THEN @rn := @rn+1 
            ELSE @rn := 1 END AS rnk,
       @d := dayname
  FROM
   (SELECT dayname, amount
     FROM mytable
     GROUP BY dayname, amount) t1
   CROSS JOIN (SELECT @d := NULL, @rn := 0) rnm
  ORDER BY dayname, amount DESC) v
ON mytable.dayname=v.dayname AND mytable.amount=v.amount
WHERE rnk <= 3;

Demo fiddle

Upvotes: 1

ProDec
ProDec

Reputation: 5410

use rank() in MySQL window function

select * 
from (
  select *, rank() over(partition by day order by amount desc) rk
  from table1
) r
where r.rk in (1,2,3)

with table data

create table table1 (
 day varchar(10),
 name varchar(100),
 amount decimal(10,2)
) ;
insert into table1 values 
('Monday','Rico',125),
('Monday','Liam',120),
('Monday','Erin',110),
('Tuesday','Maya',150),
('Tuesday','John',150),
('Tuesday','Sern',120),
('Tuesday','Rico',110),
('Wednesday','Maya',500);

Upvotes: 0

Related Questions