These articles with real life examples are great to help people get rid of cursors and change the mentalities.
I just wanted to make 2 comments:
For sample data, create a table (even a temp table) to prevent repeating the creation of sample data each time to concentrate in the real solution. This will also help define the correct data types.
CREATE TABLE MYTABLE(
INSERT INTO MYTABLE
(1, 'ORDER1', 'DS', '20151001'),
(2, 'ORDER2', 'DS', '20151001'),
(5, 'ORDER2', 'DS', '20151002'),
(3, 'ORDER3', 'DS', '20151001'),
(6, 'ORDER3', 'AG', '20151002'),
(8, 'ORDER3', 'AG', '20151003'),
(4, 'ORDER4', 'DS', '20151001'),
(7, 'ORDER4', 'AG', '20151002'),
(9, 'ORDER4', 'DS', '20151003');
Second, I found that there might be an alternative that can give a simpler plan. I'm not sure if that means better performance, but it usually helps. The secret is to reuse your windows as much as possible.
ROW_NUMBER() --Identify the latest row for each ORDER
OVER (PARTITION BY ORDER_ID ORDER BY ORDER_DATE DESC) AS ROW_NUM,
LEAD(H1.CODE,1) --Get the code 1 row before the current row.
OVER (PARTITION BY ORDER_ID
ORDER BY ORDER_DATE DESC) AS PREVIOUS_CODE
FROM MYTABLE H1
ID, ORDER_ID, CODE, PREVIOUS_CODE, ORDER_DATE
ROW_NUM = 1
AND CODE != PREVIOUS_CODE
AND PREVIOUS_CODE IS NOT NULL;
How to post data/code on a forum to get the best help: Option 1 / Option 2
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?