mysql - Limit rows in SQL for each group with Joins -
i trying limit rows each group of output. way it. explaining case below:
table1: customer_details
customer id vandeur pid_01 vandeur pid_02 vandeur pid_03 vandeur pid_04 vandeur pid_01 vandeur pid_02 vandeur pid_03 vandeur pid_04 vandeur pid_05
table2: month_details
month id jan pid_01 jan pid_02 jan pid_03 jan pid_04 feb pid_01 feb pid_02 feb pid_03 feb pid_04 feb pid_05
my output requirement - want first 3 ids of each month customer vandeur
sample o/p:
customer month id vandeur jan pid_01 vandeur jan pid_02 vandeur jan pid_03 vandeur feb pid_01 vandeur feb pid_02 vandeur feb pid_03
can suggest me ways achieve it.
mysql doesn't support window functions other rdbms,to n results per group can use user defined variables
select distinct c.customer,m1.`month`,c.id customer_details c join ( select m.*, @r:= case when @g = `month` @r +1 else 1 end rank, @g:=`month` `month_details` m , (select @g:=null,@r:=null) r order m.`month` desc ,replace(m.id,'pid_','') * 1 ) m1 on(c.id = m1.id) m1.rank <=3 , c.customer= 'vandeur' order m1.`month` desc ,replace(m1.id,'pid_','') * 1