CODEWITHSUNDEEP
mysqlsql
Harry Matharoo
Harry Matharoo

Reputation: 110

Mysql group limited data from table

I have a account table i want to take all the recipient names who get more or equal to 1024 amount within 3 biggest transaction.

I am attaching a image to demonstrate data in table, the result of these will be two rows with recipient name nutan and vicky

enter image description here

--

select recipient from accounts group by recipient having sum(amount) >= 1024 and count(*) <=3

Upvotes: 0

Views: 245

Answers (2)

Harry Matharoo
Harry Matharoo

Reputation: 110

I found the solution feeling like superhero now.

set @num=1,@recp:='';
select 
@recp:=recipient as recipient
from 
(select recipient,amount from accounts order by recipient,amount desc) as accounts
where (@num:=if(@recp=recipient,@num+1,1))<4
group by recipient
having sum(amount)>=1024
order by recipient,amount desc;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270301

You need to get the top three transactions. Possibly the simplest method is to use variables:

select recipient
from (select a.*,
             (@rn := if(@r = recipient, @rn + 1,
                        if(@rn := recipient, 1, 1)
                       )
             ) as rn
      from accounts a cross join
           (select @rn := 0, @r := '') params
      order by recipient, amount desc
     ) a
where rn <= 3
group by recipient
having sum(amount) >= 1024;

Upvotes: 1

Related Questions