CODEWITHSUNDEEP
sqlsql-serverdatabase
Deqing
Deqing

Reputation: 14632

How to get previous row in SELECT conditionally?

In this question there are several ways to visit previous row in a SELECT statement. However, I cannot figure out how to do it conditionally.

For example, suppose we have a Transactions table:

customer_id  purchase_date  merchandise_type  merchandise_name
-----------------------------------------------------------------
1            12 Apr         Rice              Jasmine
1            18 Apr         Rice              Basmati 
1            19 Apr         Rice              Long-Grain
3            13 Apr         Rice              Jasmine

I'd like to find out how long a customer changed his/her mind after buying an item, expected output is:

customer_id  merchandise_name  days
------------------------------------
1            Jasmine           6
1            Basmati           1

Customer 1 bought Jasmine rice then bought Basmati rice 6 days later, so "days" in the first record is 6. Following code is able to do this:

select customer_id, merchandise_name,
       purchase_date - LAG(purchase_date) over (order by purchase_date) as days
from Transactions

However, it won't work when there are other types of merchandise:

customer_id  purchase_date  merchandise_type  merchandise_name
-----------------------------------------------------------------
1            12 Apr         Rice              Jasmine
1            13 Apr         Cafe              Maxwell
1            18 Apr         Rice              Basmati 
1            19 Apr         Rice              Long-grain
1            19 Apr         Cafe              Nescafe
3            13 Apr         Rice              Jasmine
3            14 Apr         Cafe              Nescafe

Is it possible to get a previous row with some condition? something like:

...
order by purchase_date
where LAG(merchandise_type) = merchandise_type

Upvotes: 0

Views: 313

Answers (1)

cha
cha

Reputation: 10411

What you are looking for is the PARTITION BY clause in your OVER function:

select customer_id, merchandise_name,
       purchase_date - 
         LAG(purchase_date) over (partition by customer_id, merchandise_type
                                  order by purchase_date) as days
from Transactions

Without this clause you will get any previous value for the purchase_date.

Upvotes: 2

Related Questions