MySQL – How to Select Next Row Only If Different from Current Row

MySQL

I have a table called times that looks like so:

id  start_date  breakfast   lunch       snack       dinner  
1   2016-10-10  06:30:00    01:30:00    05:00:00    08:00:00    
2   2016-10-17  06:30:00    01:30:00    05:00:00    08:00:00    
3   2016-10-24  06:30:00    01:30:00    04:45:00    08:00:00    
4   2016-10-31  06:30:00    01:30:00    04:45:00    08:00:00    
5   2016-11-06  06:00:00    01:00:00    03:30:00    08:00:00    
6   2016-11-14  06:10:00    01:00:00    03:30:00    08:00:00    
7   2016-11-21  06:20:00    01:00:00    03:30:00    08:00:00    
8   2016-11-28  06:25:00    01:00:00    03:30:00    08:00:00

This represents 4 times during the day. So starting on 2016-10-10, the times are 6:30am, 1:30pm, 5pm, 8pm; and starting on 2016-10-24, the snack time changes to 4:45 and all other times remain the same.

Some of the rows are duplicates, with the exception of the start_date, which represents the beginning of the week in this context.

I want to show when the next time change is, ie, select the next row when the time for breakfast, lunch, snack or dinner are different than today's.

I tried using group by like so, but it returns the row with ID 2, when I want it to return the row with ID 3:

SELECT * FROM times 
WHERE start_date > '2016-10-16' 
order by start_date asc 
group by breakfast,lunch,snack,dinner 
limit 1

One more clarification, start_date is a DATE type column, the other 4 are TIME type columns. Id is just an int.

Best Answer

You need to join the table to itself. One instance of the table to find the values of the "current" row and the second to look for the first row that the values differ.

Since the date parameter may not exist in the table, we also need a subquery (derived table, named c) to find the previous row that holds the values for the "current" date:

SELECT 
        s.* 
FROM 
        ( SELECT start_date, breakfast, lunch, snack, dinner
          FROM times
          WHERE start_date <= DATE '2016-10-16'
          ORDER BY start_date DESC
          LIMIT 1 
        ) AS c        -- "current"
    JOIN
        times AS s    -- "search"
    ON
            s.start_date > c.start_date
        AND 
            (s.breakfast, s.lunch, s.snack, s.dinner) 
            <>
            (c.breakfast, c.lunch, c.snack, c.dinner)
ORDER BY 
        s.start_date ASC 
    LIMIT 1 ;

Tested at SQLfiddle.

  • This part:

            (s.breakfast, s.lunch, s.snack, s.dinner) 
        <>
        (c.breakfast, c.lunch, c.snack, c.dinner)

    is just a compact way to write:

            (  s.breakfast <> c.breakfast
        OR s.lunch     <> c.lunch
        OR s.snack     <> c.snack
        OR s.dinner    <> c.dinner
        )

  • I assumed that the four time columns are not nullable. If they are nullable, the above expression would need to be more complicated.

Related Question