CODEWITHSUNDEEP
mysqlsql
Jorge Muñoz
Jorge Muñoz

Reputation: 651

Obtain most recent elements of each group (most recent elements) in easier way

I have a schedule table where I can group certain events as the same event using the recurring_id data. Problem is I require to obtain only one event (the most recent one) on a list of events grouped by recurring_id, but if recurring_id is 0 I just need the list, 5 elements top. Events table:

+----+---------------------+---------------+
| id |        start        | recurrence_id |
+----+---------------------+---------------+
|  1 | 2013-10-03 03:30:00 |             0 |
|  2 | 2013-10-04 03:30:00 |             0 |
|  3 | 2013-10-05 03:30:00 |             1 |
|  4 | 2013-10-12 03:30:00 |             1 |
|  5 | 2013-10-19 03:30:00 |             1 |
|  6 | 2013-10-26 03:30:00 |             1 |
|  7 | 2013-10-13 03:30:00 |             2 |
|  8 | 2013-10-06 03:30:00 |             2 |
+----+---------------------+---------------+

And I had this query (assume current date is 2013-10-03T21:18:10+00:00)

SELECT * FROM
((
    SELECT * FROM events e JOIN
    (
        SELECT recurrence_id, MIN(start) start FROM events 
        WHERE recurrence_id <> 0
        AND start > '2013-10-03T21:18:10+00:00' 
        GROUP BY recurrence_id
    ) subq USING (recurrence_id, start)
    ORDER BY start ASC
    LIMIT 5
) UNION (
    SELECT * FROM events e 
    WHERE start > '2013-10-03T21:18:10+00:00' 
    AND recurrence_id = 0
    ORDER BY start ASC
    LIMIT 5
)) sq
ORDER BY start ASC
LIMIT 5

It does the job but I think I over complicated it, also it becomes very difficult to create indexes that work :S

Expected ouput would be:

+----+---------------------+---------------+
| id |        start        | recurrence_id |
+----+---------------------+---------------+
|  2 | 2013-10-04 03:30:00 |             0 |
|  3 | 2013-10-05 03:30:00 |             1 |
|  8 | 2013-10-06 03:30:00 |             2 |
+----+---------------------+---------------+

Upvotes: 2

Views: 41

Answers (1)

Mosty Mostacho
Mosty Mostacho

Reputation: 43494

The most efficient way to get the data is by ranking using a couple of user defined variables:

SELECT ID, recurrence_id, start
FROM ( 
  SELECT 
    ID, recurrence_id, start,
    @rownum := IF(@prev = recurrence_id, @rownum + 1, 1) rank,
    @prev := recurrence_id 
  FROM events, (SELECT @rownum := NULL, @prev := NULL) init
  WHERE start > '2013-10-03T21:18:10+00:00'
  ORDER BY recurrence_id, start
) s 
WHERE (recurrence_id = 0 AND rank <= 5) OR (recurrence_id != 0 AND rank = 1)
ORDER BY recurrence_id, start

Based on your sample data, this would output:

| ID | RECURRENCE_ID |                     START |
|----|---------------|---------------------------|
|  2 |             0 | October, 04 2013 03:30:00 |
|  3 |             1 | October, 05 2013 03:30:00 |
|  8 |             2 | October, 06 2013 03:30:00 |

Upvotes: 1

Related Questions