Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Get first/last n records per group by

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

like image 581
un_montagnard Avatar asked Apr 17 '12 12:04

un_montagnard


People also ask

How do I get the first and last record in a GROUP BY SQL?

To get the first and last record, use UNION. LIMIT is also used to get the number of records you want.

How do I get last record by GROUP BY?

Retrieving the last record in each group using GROUP BY There are two solutions explained here using the GROUP BY clause. In both these solutions, we will be using the MAX() function to get the maximum value of id and then retrieving the other columns corresponding to this maximum id.

How do you get the first record of each group in SQL?

The first way to find the first row of each group is by using a correlated subquery. In short, a correlated subquery is a type of subquery that is executed row by row. It uses the values from the outer query, that is, the values from the query it's nested into.

How do I get the first row in a GROUP BY?

To do that, you can use the ROW_NUMBER() function. In OVER() , you specify the groups into which the rows should be divided ( PARTITION BY ) and the order in which the numbers should be assigned to the rows ( ORDER BY ). You assign the row numbers within each group (i.e., year).


2 Answers

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...

like image 79
Tomas Avatar answered Oct 16 '22 21:10

Tomas


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

like image 42
Karol Avatar answered Oct 16 '22 20:10

Karol