I'm writing a select statement to get the top 3 most recent items for a list of users. An example can be seen in this fiddle: http://sqlfiddle.com/#!9/cb25a4/12

The rows are getting ordered correctly using sessions variable which are set to 0 in a dummy FROM statement so as to not mess up future session requests. However, when I want to limit rows in the outer SELECT by row_num < 4, the increments I have in the SELECT clause are hit twice... or something.

Is the HAVING clause causing these variables to increments twice then? That's what it looks like. If so, so there any way I can get around this? Thanks.

3 Answers 3


Found a way to do this, though it feels like a hack and I hope somebody is able to find a different/more elegant way. The sessions variables are incremented once in WHERE and HAVING clauses, so I just added a conditional to see whether or not the column has been incremented yet. Now it's only getting incremented in the HAVING clause. Example of my hacky fix: http://sqlfiddle.com/#!9/cb25a4/35


Rearrange the subqueries; the nesting you have seems to be the problem:

        SELECT  main.*,
                @row_num := IF(@dummy1 = main.user_id,
                               @row_num := @row_num + 1, 1) AS row_num,
                @dummy1 := main.user_id AS dummy1
            FROM  listens AS main
                SELECT  @row_num := 0, @dummy1 := 0
            ) AS init
            ORDER BY  user_id, track_id 
    ) AS x
    WHERE  row_num < 4
    ORDER BY  user_id, created_at DESC 
  • Looks great, but I believe that by putting the WHERE row_num < 4 and ORDER BY created_at in the outer query, we're not guaranteeing chronological ordering of rows in the subquery. Therefore we might be filtering out rows in the outer WHERE that chronologically should be included. Thoughts?
    – AlexOlsen
    Aug 19, 2016 at 20:34
  • Your concern is probably correct. Still, I think I have pointed you in the right direction.
    – Rick James
    Aug 19, 2016 at 20:36

It seems to be a bug present only if HAVING clause is used.

If you add an arbitrary GROUP BY clause however, the problem disappears.

    SELECT main.*,
      @row_num := IF(@dummy1 = main.user_id, @row_num := @row_num + 1, 1) AS row_num,
      @dummy1 := main.user_id AS dummy1
      (SELECT * FROM listens ORDER BY user_id, track_id) AS main, 
      (SELECT @row_num := 0, @dummy1 := 0) AS dummy
    -- added GROUP BY
    GROUP BY main.user_id, main.track_id, main.created_at
    HAVING row_num < 10     
    ORDER BY user_id, created_at DESC

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.