Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Windows Functions: Tell me when that changes.


Windows Functions: Tell me when that changes.

Author
Message
michael.f.morin
michael.f.morin
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 130
Comments posted to this topic are about the item Windows Functions: Tell me when that changes.
hartmann 74688
hartmann 74688
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 77
Just to make it clear, they are windowing functions or window functions.
Windows functions sounds like API of the OS Smile.

Good Article, great reminder!
Alan.B
Alan.B
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3906 Visits: 7568
hartmann 74688 (2/17/2016)
Just to make it clear, they are windowing functions or window functions.
Windows functions sounds like API of the OS Smile.

Good Article, great reminder!


I noticed that too (and have made the same mistake). I don't know the SSC protocol for correcting articles but I hope that gets sorted out so this excellent article has a better chance of showing up earlier in a Google search.

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
michael.f.morin
michael.f.morin
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 130
Thanks for the comments guys. Nomenclature is important and I will see what SSC can do to update the article.
adame
adame
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 100
Alternative method for those without LEAD() and LAG()


WITH cteSAMPLEDATA
AS (--Create Sample data
SELECT ID, ORDER_ID, CODE, ORDER_DATE FROM
(VALUES
(1, 'ORDER1', 'DS', N'20151001'),
(2, 'ORDER2', 'DS', N'20151001'),
(5, 'ORDER2', 'DS', N'20151002'),
(3, 'ORDER3', 'DS', N'20151001'),
(6, 'ORDER3', 'AG', N'20151002'),
(8, 'ORDER3', 'AG', N'20151003'),
(4, 'ORDER4', 'DS', N'20151001'),
(7, 'ORDER4', 'AG', N'20151002'),
(9, 'ORDER4', 'DS', N'20151003')
)
AS MYTABLE(ID, ORDER_ID, CODE, ORDER_DATE)
)


SELECT A.ID
,A.ORDER_ID
,A.CODE
,A.ORDER_DATE AS ORDER_DATE
,B.ID AS PREVIOUS_ID
,B.CODE AS PREVIOUS_CODE
,B.ORDER_DATE AS PREVIOUS_ORDERDATE
FROM (
SELECT DISTINCT X.*
FROM cteSAMPLEDATA C
CROSS APPLY(
SELECT TOP 1 *
FROM cteSAMPLEDATA Z
WHERE Z.ORDER_ID = C.ORDER_ID
ORDER BY Z.ORDER_DATE DESC
) X
) A
CROSS APPLY(
SELECT TOP 1 *
FROM cteSAMPLEDATA Z
WHERE Z.ORDER_ID = A.ORDER_ID
AND Z.ORDER_DATE<A.ORDER_DATE
ORDER BY Z.ORDER_DATE DESC
) B
WHERE A.CODE<>B.CODE


dgreen-1126628
dgreen-1126628
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 82
Great article. And since you are going to clean it up for "Windows" vs "Windowing", you can clean up the following typos, too Smile

1. "Meaning it is the only order whose most recent date (2015-10-03) has a different code (DS) that the one before it (AG) from 2015-10-02"
("that" should be "than")

2. “(note this is for SQL Server 2012+ and Azure):
“Accesses data from a subsequent row in the same result set without the use of a self-join in SQL Server 2016. ..."
(confusing you quote from SQL Server 2016 when saying it is ok in 2012+)

3. "Since our rows as sorted by ORDER_DATE descending, that would be what we want." ("as" should be "are")

4. "LEAD(H1.CODE,1) --Get the code 1 row after the current row.
OVER (PARTITION BY ORDER_ID
ORDER BY ORDER_DATE DESC) AS PREVIOUS_CODE"
"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" (first comment says "after" but second occurance says "before")
timwell
timwell
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1305 Visits: 3024
Thanks Michael for doing this article. I thought it was a good explanation and example.
However I was also distracted by the window/windows difference and some typos / misspellings.

Here are a couple more I found:

"They wanted to know when the most resent order’s code was different from the one previous to it." (I think you meant "recent," not that it was re-sent. It seems to be correct everywhere else)

"In this case we want to partition our recordset by ORDER_ID, essentially grouping all rows with the sam ORDER_ID together."
(sam/same)
Siberian Khatru
Siberian Khatru
SSC Veteran
SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)

Group: General Forum Members
Points: 205 Visits: 391
Great content, other than the typos and nomenclature things others have already mentioned. Thanks!
j-1064772
j-1064772
Mr or Mrs. 500
Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)

Group: General Forum Members
Points: 563 Visits: 1206
Re: Alternative method for those without LEAD() and LAG()

The original code WITH LEAD() and LAG() seems easier to read for me.

The alternative method would apply in a version of SSQL which does not support LEAD or LAG. Otherwise, time to look at using the new features.

Maybe someone who is an old hand at this could compare the actual execution plans to see which method scales better ?
davidawest00
davidawest00
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 142
I have encountered this precise scenario a dozen times.

You can do the same thing with a correlated sub-query, and they usually scale better than any CTE because a CTE is just an in-line view, which SQL Server will generate first.
In other words, SS will create ALL THE ROWS for a CTE before it processes the main query, whether it will need all those rows or not, and you can't put an index on a CTE.
Slow and disk intensive. Bleh! Better to create 2 temp tables and index them: 1 for the current order and 1 for the previous order.
Then join. Last time I did that the stored proc went from 2 hours to 20 minutes.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search