I have written this SQL after browsing different topics. The topic I got the solution from -> Limit Records
SELECT op.* 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:
@rn being used as an iterator to loop through to retrieve back 10 products ?
@s coming from ? Why do we need a cross join there and what
(select @s := 0, @rn := 0) params?
Thanks so much in advance!!!