Here is a simple example. The MY_LOCATION table stores my locations over time as I move from room to room in an old PC adventure game.
Code: Select all
dbadmin=> SELECT * FROM my_location;
col1 | col2 | col3
------+-----------------+----------
1 | First Location | Room 10
2 | Second Location | Room 6
3 | Third Location | Room 55
4 | Forth Location | Room 55
5 | Fifth Location | Room 666
(5 rows)
Code: Select all
dbadmin=> SELECT col1, col2 || '(' || col3 || ')' where_am_i,
dbadmin-> LAG( col2 || '(' || col3 || ')', 1) OVER (ORDER BY col1) where_was_i,
dbadmin-> LEAD( col2 || '(' || col3 || ')', 1) OVER (ORDER BY col1) where_i_am_going
dbadmin-> FROM my_location;
col1 | where_am_i | where_was_i | where_i_am_going
------+--------------------------+-------------------------+--------------------------
1 | First Location(Room 10) | | Second Location(Room 6)
2 | Second Location(Room 6) | First Location(Room 10) | Third Location(Room 55)
3 | Third Location(Room 55) | Second Location(Room 6) | Forth Location(Room 55)
4 | Forth Location(Room 55) | Third Location(Room 55) | Fifth Location(Room 666)
5 | Fifth Location(Room 666) | Forth Location(Room 55) |
(5 rows)