CODEWITHSUNDEEP
mysqlsql
ekaizawa
ekaizawa

Reputation: 3

Select the first 3 occurrences of each ID

I have the following table

ID | date   
1   | 2016-01-01   
1   | 2016-01-02   
1   | 2016-02-01   
1   | 2016-03-01  
1   | 2016-03-11   
2   | 2016-01-01   
2   | 2016-01-03   
2   | 2016-01-05   
2   | 2016-02-01   
2   | 2016-03-01   
3   | 2016-01-03   
3   | 2016-01-04   
3   | 2016-01-05   
3   | 2016-01-06

I need the query to return the first 3 dates from each ID, the result should be: 

ID | date   
1   | 2016-01-01   
1   | 2016-01-02   
1   | 2016-02-01   
2   | 2016-01-01   
2   | 2016-01-03   
2   | 2016-01-05   
3   | 2016-01-03   
3   | 2016-01-04   
3   | 2016-01-05

Upvotes: 0

Views: 118

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72235

You can use variables for this:

SELECT ID, `date`
FROM (
  SELECT ID, `date`,
         @rn := IF(@id = ID, @rn + 1,
                   IF(@id := ID, 1, 1)) AS rn
  FROM mytable
  CROSS JOIN (SELECT @rn := 0, @id := 0) AS vars
  ORDER BY ID, `date`) AS t
WHERE t.rn <= 3

Upvotes: 4

Related Questions