Next value by previous data

Question

I am trying to create a query that will query the next result by looking into data as how it previous was.

So my data is like

Train - Station   -  Time
158     Station1     11:10
158     Station1     11:11
158     Station1     11:12
158     Station1     11:13
158     Station1     11:14
158     Station2     11:25
158     Station2     11:26
158     Station2     11:27
158     Station3     11:41
158     Station3     11:42
158     Station3     11:43
158     Station3     11:44
158     Station3     11:45
158     Station4     11:50
158     Station4     11:51
158     Station4     11:52
158     Station4     11:53

So lets say im at "Station3"

I am using the following query to find out what previous Station is:

SELECT * FROM Train 
WHERE Train = '158' AND Station NOT LIKE 'Station3' 
ORDER BY Time DESC 
LIMIT 1

I want to query what the next Station is, but i cant really figure out to make that query. Any tips?

Edit: Ok, lets say that my table have more information, about previous train runs, and it is from that i want to check what the next station will be.

Train - Station - Time
158 Station1 10:10
158 Station1 10:11
158 Station1 10:12
158 Station1 10:13
158 Station1 10:14
158 Station2 10:25
158 Station2 10:26
158 Station2 10:27
158 Station3 10:41
158 Station3 10:42
158 Station3 10:43
158 Station3 10:44
158 Station3 10:45
158 Station4 10:50
158 Station4 10:51
158 Station4 10:52
158 Station4 10:53
158 Station5 10:55
158 Station5 10:56
158 Station6 10:57
158 Station6 10:58
158 Station1 11:10
158 Station1 11:11
158 Station1 11:12
158 Station1 11:13
158 Station1 11:14
158 Station2 11:25
158 Station2 11:26
158 Station2 11:27
158 Station3 11:41
158 Station3 11:42
158 Station3 11:43
158 Station3 11:44
158 Station3 11:45
158 Station4 11:50
158 Station4 11:51
158 Station4 11:52
158 Station4 11:53
159 Station1 11:10
159 Station1 11:11
159 Station1 11:12
159 Station1 11:13
159 Station1 11:14
159 Station2 11:25
159 Station2 11:26
159 Station2 11:27
159 Station3 11:41
159 Station3 11:42
159 Station3 11:43
159 Station3 11:44
159 Station3 11:45
159 Station4 11:50
158 Station4 11:51
159 Station4 11:52
159 Station4 11:53

So lets say im at the Train 158 Station4 Time 11:53, and i dont know what the next Station is, and i want to query it is Station5. How would i be doing that?

Answer 1

Other approach with out first finding the MAX(time) for station.

MySQL 8.0+ makes it really eazy with LEAD to get the next records data.

Query

SELECT
   Table1.Train
 , Table1.next_station AS station
 , Table1.next_station_time AS time
FROM ( 

   SELECT 
     *
     , LEAD(Station) OVER (PARTITION BY Train ORDER BY Time) AS next_station
     , LEAD(Time) OVER (PARTITION BY Train ORDER BY Time) AS next_station_time
   FROM
    Table1
   WHERE
     Train = 158 
)
 AS Table1

WHERE
   Table1.station = 'station3'
 AND
   Table1.station <> Table1.next_station

see demo https://www.db-fiddle.com/f/9Ld7XznMEuzNdRC3dmysUt/1

In the older MySQL versions we need to be more creative because LEAD isn't supported.
Best way to simulate LEAD is by using MySQL's user variables, and a shifting self LEFT JOIN
Keep in mind MySQL user variables work in MySQL 5.1+

Query

SELECT 
   current_train AS train
 , next_station AS station
 , next_time AS time
FROM ( 

  SELECT 
      t1.Train AS current_train
    , t1.Station AS current_station
    , t1.Time AS `current_time`
    , t2.Train AS next_train
    , t2.Station AS next_station
    , t2.Time AS next_time
  FROM (
    SELECT 
      *
      , @rownum1 := @rownum1 + 1 AS rownum
    FROM 
      Table1
    CROSS JOIN ( SELECT @rownum1 := 0 ) AS i 
    WHERE
      Train = 158
    ORDER BY 
      Time ASC
  ) AS t1
  LEFT JOIN (
    SELECT 
      *
      , @rownum2 := @rownum2 + 1 AS rownum
    FROM 
      Table1
    CROSS JOIN ( SELECT @rownum2 := 0 ) AS i   
    WHERE
      Train = 158 
    ORDER BY 
      Time ASC 
  ) AS t2
  ON   
   t1.rownum + 1 = t2.rownum
 ) 
 AS records
WHERE
   records.current_station = 'station3'
 AND
   records.current_station <> records.next_station

See demo http://sqlfiddle.com/#!9/ff23fc/38

Answer 2

On a simpler way, using self join could help you achieve it.

Try this:

select t1.* from train t1 
right join train t2 on t1.train=t2.train
where t1.station != 'Station3' and t1.time>(t2.time)
and t2.station='Station3' limit 1 ;

Try changing the station value on both table t1 and t2 to your desired value which is Station3 in this case.

Live SQL Fiddle demo here.

Answer 3

if you only need to know the next station you could use a subquery for max time for station3 and train 158

  select * from 
  my_table 
  where time > ( 
  select max(time) max_time
  from my_table 
  where Train = '158' AND Station = 'Station3' )
  and train = '158'
  order by time limit 1