Getting an element and the next from a table

I have a table with ids, cities and some sequence number, say:

ID  CITY         SEQ_NO
1   Milan        123
2   Paris        124
1   Rome         125
1   Naples       126
1   Strasbourg   130
3   London       129
3   Manchester   132
2   Strasbourg   128
3   Rome         131
2   Rome         127
4   Moscow       135
5   New York     136
4   Helsinki     137

I want to get the city that comes after Rome for the same id, in this case, I can order them by doing something like:

SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY SEQ_NO) as rownum, 
       id, 
       city, 
       seq_no 
  FROM mytable

I get:

rownum  ID  CITY         SEQ_NO
1        1   Milan        123
2        1   Rome         125
3        1   Naples       126
4        1   Strasbourg   130
1        2   Paris        124
2        2   Rome         127
3        2   Strasbourg   128
1        3   London       129
2        3   Rome         131
3        3   Manchester   132
1        4   Moscow       135
2        4   Helsinki     137
1        5   New York     136

and, I want to get

ID  CITY         SEQ_NO
1   Rome         125
1   Naples       126
2   Rome         127
2   Strasbourg   128
3   Rome         131
3   Manchester   132

How do I proceed?

Answer

Hmmm . . . I might suggest window functions:

select t.*
from (select t.*,
             lag(city) over (partition by id order by seq_no) as prev_city
      from mytable t
     ) t
where 'Rome' in (city, prev_city)

Advertisement

Answer