MySQL conditional query for next row

Question

I have a table called Session in MySQL which looks like this:

+-----+---------------------+---------------------+--------------------+
| id  | start               | finish              | name               |
+-----+---------------------+---------------------+--------------------+
| -12 | 2013-04-27 09:00:00 | 2013-04-27 13:00:00 | Saturday Setup 1   |
| -11 | 2013-04-27 13:00:00 | 2013-04-27 18:00:00 | Saturday Setup 2   |
| -10 | 2013-04-27 23:00:00 | 2013-04-28 08:00:00 | Saturday Night     |
|  -3 | 2013-04-28 08:00:00 | 2013-04-28 13:00:00 | Sunday Setup 1     |
|  -2 | 2013-04-28 13:00:00 | 2013-04-28 18:00:00 | Sunday Setup 2     |
|  -1 | 2013-04-28 23:00:00 | 2013-04-29 08:00:00 | Sunday Night       |
|   1 | 2013-04-29 09:00:00 | 2013-04-29 13:00:00 | Monday Setup 1     |
|   2 | 2013-04-29 13:00:00 | 2013-04-29 17:00:00 | Monday Setup 2     |
|   3 | 2013-04-29 17:00:00 | 2013-04-29 21:00:00 | Monday Setup 3     |
|   4 | 2013-04-29 23:00:00 | 2013-04-30 08:00:00 | Monday Night       |
|  10 | 2013-04-30 09:00:00 | 2013-04-30 13:00:00 | Tuesday Setup 1    |
|  11 | 2013-04-30 13:00:00 | 2013-04-30 17:00:00 | Tuesday Setup 2    |
|  12 | 2013-04-30 23:00:00 | 2013-05-01 08:00:00 | Tuesday Night      |
more rows etc...

I want to create a query which will select all columns in the table, with an additional column showing the next id on the same day (if any) when ordered by Start.

The result would look like this:

+-----+--------+---------------------+---------------------+--------------------+
| id  | nextid | start               | finish              | name               |
+-----+--------+---------------------+---------------------+--------------------+
| -12 |    -11 | 2013-04-27 09:00:00 | 2013-04-27 13:00:00 | Saturday Setup 1   |
| -11 |    -10 | 2013-04-27 13:00:00 | 2013-04-27 18:00:00 | Saturday Setup 2   |
| -10 |        | 2013-04-27 23:00:00 | 2013-04-28 08:00:00 | Saturday Night     |
|  -3 |     -2 | 2013-04-28 08:00:00 | 2013-04-28 13:00:00 | Sunday Setup 1     |
|  -2 |     -1 | 2013-04-28 13:00:00 | 2013-04-28 18:00:00 | Sunday Setup 2     |
|  -1 |        | 2013-04-28 23:00:00 | 2013-04-29 08:00:00 | Sunday Night       |
|   1 |      2 | 2013-04-29 09:00:00 | 2013-04-29 13:00:00 | Monday Setup 1     |
|   2 |      3 | 2013-04-29 13:00:00 | 2013-04-29 17:00:00 | Monday Setup 2     |
|   3 |      4 | 2013-04-29 17:00:00 | 2013-04-29 21:00:00 | Monday Setup 3     |
|   4 |        | 2013-04-29 23:00:00 | 2013-04-30 08:00:00 | Monday Night       |
|  10 |     11 | 2013-04-30 09:00:00 | 2013-04-30 13:00:00 | Tuesday Setup 1    |
|  11 |     12 | 2013-04-30 13:00:00 | 2013-04-30 17:00:00 | Tuesday Setup 2    |
|  12 |        | 2013-04-30 23:00:00 | 2013-05-01 08:00:00 | Tuesday Night      |
more rows etc...

Any suggestions would be appreciated...

eggyal · Accepted Answer · 2013-04-23 13:46:21Z

1

It won't be very efficient, as indexes cannot be used to evaluate the self-join criterion DATE(..) = DATE(..), but you're essentially trying to obtain the groupwise minimum:

SELECT cur.id, nxt.id nextid, cur.start, cur.finish, cur.name
FROM (
  SELECT      s1.*, MIN(s2.start) next
  FROM        Session s1
    LEFT JOIN Session s2
           ON DATE(s1.start) = DATE(s2.start)
          AND s1.start < s2.start
  GROUP BY    s1.start
) cur LEFT JOIN Session nxt ON cur.next = nxt.start

See it on sqlfiddle.

answered Apr 23, 2013 at 13:46

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Manee.O.H · Accepted Answer · 2013-04-23 13:50:31Z

1

I am not sure but you can try this

create a view for the session table name it seesion_view you can select the additional column showing the next id on the same day
then you can select the data you wont from both the table and the seesion_view.
i am not sure if using join is OK but you can try to use it also

Lastly your MySql should be 5.4+

answered Apr 23, 2013 at 13:50

Manee.O.H

5898 silver badges19 bronze badges

1 Comment

eggyal Over a year ago

What's MySQL 5.4? I thought versions went from 5.1 to 5.5? In any event, what feature(s) in this answer are unavailable in earlier versions? Could you furnish your answer with some examples showing how to use a view in the fashion you describe? It's unclear what you mean by your third point.

Gordon Linoff · Accepted Answer · 2013-04-23 14:13:13Z

1

This is a place where I think correlated subqueries are useful:

select id,
       (select id from session s2 where date(s.start) = date(s2.start) order by start desc limit 1
       ) as nextId
       start, finish, name
from session s

answered Apr 23, 2013 at 14:13

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Comments

Kickstart · Accepted Answer · 2013-04-23 13:50:26Z

0

Using the start date to decide if the days match, something like this should do it. A subselect to get the min id which is larger than another id for the same day. Not tested I'm afraid.

SELECT a.id, Sub1.MinId AS nextid, a.start, a.finish, a.name  
FROM SomeTable a
LEFT OUTER JOIN (SELECT a.id, MIN(b.id) AS MinId
FROM SomeTable a
INNER JOIN SomeTable b
ON DATE(a.start) = DATE(b.start)
AND a.id < b.id
GROUP BY a.id) Sub1
ON a.id = Sub1.id

Or is id is not in start order

SELECT a.id, c.id AS nextid, a.start, a.finish, a.name  
FROM SomeTable a
LEFT OUTER JOIN (SELECT a.id, MIN(b.start) AS MinStart
FROM SomeTable a
INNER JOIN SomeTable b
ON DATE(a.start) = DATE(b.start)
AND a.id < b.id
GROUP BY a.id) Sub1
ON a.id = Sub1.id
LEFT OUTER JOIN SomeTable c
ON Sub1.MinStart = c.start

edited Apr 23, 2013 at 13:50

answered Apr 23, 2013 at 13:44

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

Collectives™ on Stack Overflow

MySQL conditional query for next row

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related