CODEWITHSUNDEEP
sqlparaccel
Demetri Pananos
Demetri Pananos

Reputation: 7404

How can I order my data as indicated?

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?

Upvotes: 0

Views: 35

Answers (2)

jjaskulowski
jjaskulowski

Reputation: 2564

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

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.

Upvotes: 0

Related Questions