Internal mysql limits
I have two tables, dots and comments (for locations), comments refer to spots by spot_id column, and I want to select the last 3 comments for every place I have in db points.
I am using code from M Khalid Junaid in this question: mysql select inner join constraint
SELECT * FROM (
SELECT c.id, a.author_id, a.author_username, a.comment, a.timestamp,
@r:= CASE WHEN @g = c.id THEN @r +1 ELSE 1 END rownum,
@g:= c.id catgroup
FROM (SELECT * FROM spots ORDER BY timestamp DESC LIMIT $start_index , $rows_count) as c
JOIN comments a ON (c.id = a.spot_id)
CROSS JOIN (SELECT @g:=0,@r:=0) t2
ORDER BY c.id , a.timestamp desc
) t
WHERE rownum <= 3
But the problem is that it returns more than three rows as rownum after hitting three statistics read from 1.
This is the result for rownum <= 3
As you can see, I am getting 5 comments for place id 58 instead of 3. Am I missing something?
source to share
You shouldn't assign variables in one expression and then use them in another. MySQL does not guarantee the order in which expressions in a statement are evaluated select
. In your case, this applies to @g
.
So, the correct way to write a query is to manipulate variables in a single statement:
SELECT *
FROM (SELECT *,
(@r := IF(@g = c.id, @r + 1,
IF(@g := c.id, 1, 1)
)
) as rownum,
c.id as catgroup
FROM category c JOIN
articles a
ON c.id = a.category_id CROSS JOIN
(SELECT @g := 0, @r := 0) params
ORDER BY c.id, a.`date` desc
) t
WHERE rownum <= 5;
Of course, if you only want three results, change "5" to "3".
source to share