If I have the following table,

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,

CodePudding user response：

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);

CodePudding user response：

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