I have written this SQL after browsing different topics. The topic I got the solution from -> Limit Records

MySQL version: 5.6

FROM (select op.*,
             (@rn := if(@s = order_id, @rn + 1,
                        if(@s := order_id, 1, 1)
             ) AS rn
      from (select op.*
            from orders_products op
            where order_id in ('191497','191487')
            order by order_id, quantity ASC
           ) op cross join
           (select @s := 0, @rn := 0) params
     ) op
WHERE rn <= 10
AND op.active = 'Y';

So it does work and it only returns the maximum of 10 products for each order id that I pass in, which is amazing. My problem is that I'm struggling to understand what is actually going on in the FROM section. If anyone could break me down what's going on here I'd really appreciate that!

My vague suggestion: Is @rn being used as an iterator to loop through to retrieve back 10 products ? Where @s coming from ? Why do we need a cross join there and what (select @s := 0, @rn := 0) params?

Thanks so much in advance!!!

New contributor
olliesyke is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • Search this forum for [mysql] [greatest-n-per-group]
    – Rick James
    Aug 5 at 22:41


Your Answer

olliesyke is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.