CODEWITHSUNDEEP
mysqlselect
NickJ
NickJ

Reputation: 9559

MySQL conditional query for next row

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...

Upvotes: 1

Views: 153

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

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

Upvotes: 1

Manee.O.H
Manee.O.H

Reputation: 589

I am not sure but you can try this

  1. 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
  2. then you can select the data you wont from both the table and the seesion_view.
  3. i am not sure if using join is OK but you can try to use it also

Lastly your MySql should be 5.4+

Upvotes: 1

Kickstart
Kickstart

Reputation: 21513

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

Upvotes: 0

eggyal
eggyal

Reputation: 125835

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.

Upvotes: 1

Related Questions