CODEWITHSUNDEEP
mysqlsql
voytez
voytez

Reputation: 1842

mysql inner join limiting results

I have two tables, spots and comments(for locations), comments are referencing spots by a column spot_id and i want to pick last 3 comments per every spot I have in spots db.

I'm using the code from by M Khalid Junaid in this question: mysql select inner join limit

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 it returns more than 3 rows as rownum after hitting three stats counting from 1.

This is result for rownum <= 3

enter image description here

As you can see i get 5 comments for spot id 58 instead of just 3. Am I missing something?

Upvotes: 0

Views: 195

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271091

You should not assign variables in one expression and then use them in another. MySQL does not guarantee the order of evaluation of expressions in a select statement. In your case, this applies to @g.

So, the right way to write the query is to do the variable manipulation in a single expression:

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, then change the "5" to a "3".

Upvotes: 1

Related Questions