How can I order my data as indicated?

Question

Suppose I have some customer IDs and dates of transactions.

ID  DATE
1   OCT 1
1   OCT 2
1   OCT 3
1   OCT 31

What I would like is one column showing their previous transaction date, and another showing their next transaction date (shown below).

ID DATE1 DATE2
1  OCT 1  OCT 2
1  OCT2   OCT3
1  OCT 3  OCT 31

Here,ID is an INTEGER and DATE is a DATE.

How can I achieve this?

Please tag with the database you are using. Also, I assume the date column is really a date and not a string. — Gordon Linoff, Nov 24 '16 at 16:50

score 0 · Answer 1 · answered Nov 24 '16 at 16:51

Most dialects of SQL support the ANSI standard window functions, including lead():

select t.*
from (select t.*,
             lead(date) over (partition by id order by date) as next_date
      from t
     ) t
where next_date is not null;

For databases that don't support this functionality, you can do something similar with a subquery.

score 0 · Answer 2 · answered Nov 24 '16 at 16:54

No matter what database you're using you can simply select the results twice, both times with an enumerator column that tells the row number (for instance in T-SQL see how to use ROW_NUMBER()) , and then just join these two selects on a.enumerator = b.enumerator - 1

How can I order my data as indicated?

2 Answers2