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.


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 '16 at 20:34
  • Your concern is probably correct. Still, I think I have pointed you in the right direction. – Rick James Aug 19 '16 at 20:36

Your Answer

