Getting the latest n records for each group

Question

Lets say I have the following table:

id  coulmn_id  value    date
1      10      'a'     2016-04-01
1      11      'b'     2015-10-02
1      12      'a'     2016-07-03
1      13      'a'     2015-11-11
2      11      'c'     2016-01-10
2      23      'd'     2016-01-11
3      11      'c'     2016-01-09
3      111     'd'     2016-01-11
3      222      'c'     2016-01-10
3      333      'd'     2016-01-11

for n = 3, I want to get the latest n records<=3 for each id. So I will have the following output:

id  column_id  value    date
1      10        'a'     2016-04-01
1      12        'a'     2016-07-03
1      13        'a'     2015-11-11
2      11        'c'     2016-01-10
2      23        'd'     2016-01-11
3      111       'd'     2016-01-11
3      222       'c'     2016-01-10
3      333       'd'     2016-01-11

http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results — bill, Jul 22 '16 at 20:54
Solutions for the above problems work for a finite number of 'group_name', date field can be large.. — futurenext110, Jul 22 '16 at 20:58
primary key= id and column_id. I have updated the table accordingly. — futurenext110, Jul 22 '16 at 20:59
See questions with the tag [tag:greatest-n-per-group]. This question has been answered many times. — Bill Karwin, Jul 22 '16 at 21:01

score 5 · Answer 1 · answered Jul 22 '16 at 21:07

5

I am answering because the referenced question has an unstable answer (I'll comment on that there).

Here is a solution that should work:

select t.*
from (select t.*,
             (@rn := if(@id = id, @rn + 1,
                        if(@id := id, 1, 1)
                       )
             ) as seqnum
      from t cross join
           (select @rn := 0, @id := -1) params
      order by id, date desc
     ) t
where seqnum <= 3;

The difference in the solutions is that the variable assignments are all in a single expression. MySQL does not guarantee the order of evaluation of expressions, so this is very important if the code is going to work consistently.

answered Jul 22 '16 at 21:07

Gordon Linoff

1,242,037
58
646
786

I don't mean to hassle you across multiple answers of this type, this is more something the question poster will need to sort out, but technically this answer is potentially unstable if the `date` field is not unique. _Just adding to a good answer._ – Uueerdo Jul 22 '16 at 22:02
@Uueerdo . . . That is a reasonable observation. However, the answer is correct unless the *data* has an issue. The referenced answer can be incorrect from the perspective of the *database*. – Gordon Linoff Jul 23 '16 at 02:26

score 0 · Answer 2 · answered Jul 22 '16 at 21:08

You could do this with the use of variables. First go through the results in reverse order and assign a row number, then filter the results for row numbers less or equal to 3, and re-order:

select   id, value, date
from     (
           select      id, value, date,
                       @rn := if(@id = id, @rn+1, if (@id := id, 1, 1)) rn
           from        mytable,
           cross join  (@id := null, @rn := null) init
           order by    id, date desc
         ) as base
where    rn <= 3
order by id, date asc

Getting the latest n records for each group

2 Answers2

Linked