MySQL Select first N rows from table with rows related to another one

Question

I have a repayments table which has 16 rows for each loan that the repayments belong.

Repayments
loanid  repid  amnt
--------------------
a1      r1     1,100
a1      r2     1,100
|       |      |
a1      r16    1,105
b2      s1     2,500
b2      s2     2,500
|       |      |
b2      s16    2,510
|       |      |

Loans
loanid  othercolumns...
-----------------------
a1
b2
|
blahid
|

LoanIds are some string. RepaymentIds too

I'm looking for a query which gives me the first 15 rows from each repayments for every loan.

loanid  repid  amnt
a1      r1     1,100
a1      r2     1,100
|       |      |
a1      r15    1,105
b2      s1     2,500
b2      s2     2,500
|       |      |
b2      s15    2,510
|       |      |

Is this possible with SQL? and if so, how?

Answer 1

Assuming rep isn't sequential, in which case you can use WHERE rep <= 15, then you need to introduce a row number per group. MySql does not have a built in row number function like other databases, but you can use user defined variables to achieve the same result

select *
from (
  select loan, rep, amnt, @row:=if(@prevLoan=loan, @row+1, 1) rn, @prevLoan:=loan
  from repayments
    join (select @row:=0, @prevLoan:=0) t
  order by loan, rep
  ) t
where rn <= 15

• Condensed SQL Fiddle Demo