Mysql – Get first/last n records per group by

greatest-n-per-groupinner-joinmysqlsql

I have two tables : tableA (idA, titleA) and tableB (idB, idA, textB) with a one to many relationship between them. For each row in tableA, I want to retrieve the last 5 rows corresponding in tableB (ordered by idB).

I've tried

SELECT * FROM tableA INNER JOIN tableB ON tableA.idA = tableB.idA LIMIT 5

but it's just limiting the global result of INNER JOIN whereas I want to limit the result for each different tableA.id

How can I do that ?

Thanks

Best Solution

Much simplified and corrected Carlos solution (his solution would return first 5 rows, not last...):

SELECT tB1.idA, tB1.idB, tB1.textB
FROM tableB as tB1
    JOIN tableB as tB2
        ON tB1.idA = tB2.idA AND tB1.idB <= tB2.idB
GROUP BY tB1.idA, tB1.idB
HAVING COUNT(*) <= 5

In MySQL, you may use tB1.textB even if it is group by query, because you are grouping by the idB in the first table, so there is only single value of tB1.textB for each group...