Clever Geek Handbook

Select rows in SQL where the current column value does not match the previous value

I have a table of orders. In this table, among other rows, I have an ID (PK), a customer ID, a shipping country, and an 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 lines where the previous sales order does not match their last order delivery country. I also want to see 2 different 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

Id and OrderDate can be used to determine the order of things. The ID is an increasing number, the order date is as specified.

The table in which I need to perform this action contains about 500k rows.

Any suggestions?

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

+3


source to share


2 answers


Use ROW_NUMBER

to specify the last record # 1 and previous # 2 for each client. Then, aggregate for each customer and compare the two values.

select 
  CustomerId,
  max(case when rn = 1 then ShippingCountry end) as ShippingCountryLatest, 
  max(case when rn = 2 then ShippingCountry end) as ShippingCountryPrevious
from
(
  select 
    CustomerId,
    ShippingCountry,
    row_number() over (partition by CustomerId order by ID desc) as rn
  from orders
) numbered
group by customerid
having
  max(case when rn = 1 then ShippingCountry end) <>
  max(case when rn = 2 then ShippingCountry end);


Your game is back: http://sqlfiddle.com/#!6/5d046/13 : -)

+4


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 ;
+1


source






More articles:


All Articles