CODEWITHSUNDEEP
mysqlsql
Dinosaurius
Dinosaurius

Reputation: 8638

Select N entries for each month value

I have the following SQL table:

Id    DateTime              Speed
1     2017-03-02 19:06:20   50
1     2017-03-02 19:10:18   52
1     2017-04-01 20:01:10   55
2     2017-03-02 18:06:20   60
2     2017-05-03 19:08:00   61
3     2017-04-12 19:01:40   80
3     2017-05-11 19:05:50   82
3     2017-05-14 11:00:00   81

I want to select any 2 entries for each month, so that months 3, 4 and 5 would have 2 observations.

Can anyone help with how to do it?

EXPECTED RESULT:

Id    DateTime              Speed
1     2017-03-02 19:06:20   50
1     2017-03-02 19:10:18   52
1     2017-04-01 20:01:10   55
3     2017-04-12 19:01:40   80
2     2017-05-03 19:08:00   61
3     2017-05-11 19:05:50   82

Upvotes: 1

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271161

I think the most efficient general method in MySQL is to use variables:

select t.*
from (select t.*,
             (@rn := if(@ym = date_format(date, '%Y-%m'), @rn + 1,
                        if(@ym := date_format(date, '%Y-%m'), 1, 1)
                       )
             ) as rn
      from t cross join
           (select @ym := '', @rn := 0) params
      order by date_format(date, '%Y-%m')
     ) t
where rn <= 2;

This returns an arbitrary two rows from each month. You can add a second join key to get two particular values -- the first two, last two, highest speed or whatever.

Upvotes: 1

Related Questions