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
To get the first and last record, use UNION. LIMIT is also used to get the number of records you want.
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.
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.
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).
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...
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With