Get first/last n records per group by

greatest-n-per-group, inner-join, mysql, sql

Solution

I think this is what you need:

SELECT tableA.idA, tableA.titleA, temp.idB, temp.textB
FROM tableA
INNER JOIN
(
    SELECT tB1.idB, tB2.idA,
    (
        SELECT textB
        FROM tableB
        WHERE tableB.idB = tB1.idB
    ) as 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
    ORDER BY idA, idB
) as temp
ON tableA.idA = temp.idA

More info about this method here:

http://www.sql-ex.ru/help/select16.php

Problem

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

Original source

Related problems