gfyans gfyans - 4 months ago 19
SQL Question

Select rows in SQL where current column value doesn't match previous value

I have a table of orders. In this table, amongst other rows, I have an ID (PK), Customer ID, Shipping country, and Order date

ID | CustomerId | ShippingCountry | OrderDate
1 | 111111 | DE | 2016-08-13
2 | 222222 | GB | 2016-08-17
3 | 111111 | ES | 2016-09-05
4 | 333333 | ES | 2016-10-25
5 | 444444 | US | 2016-10-26
6 | 555555 | FR | 2016-10-29
7 | 666666 | DE | 2016-11-04
8 | 111111 | DE | 2016-11-12
9 | 222222 | US | 2016-12-01
10 | 444444 | GB | 2016-12-01
11 | 555555 | FR | 2016-12-05
12 | 333333 | ES | 2016-12-15


I need to select the rows where the customer's previous order doesn't match their latest order's shipping country. I also want to see the 2 differing shipping codes in the results.

Using the above example, I want to see:

CustomerId | ShippingCountryLatest | ShippingCountryPrevious
111111 | DE | ES
222222 | US | GB
444444 | GB | US


The ID and OrderDate can be used to determine the order of things. ID is an incrementing number, order date is as it says.

The table I need to run this against has about 500k rows.

Any suggestions?

Here's a SQLFiddle to get you started: http://sqlfiddle.com/#!6/5d046/1/0

Answer Source

Use lag():

select o.*
from (select o.*,
             lag(shippingcountry) over (partition by customerid order by orderdate) as prev_shippingcountry
      from orders o
     ) o
where prev_shippingcountry <> shippingcountry ;