November 27, 2013 at 4:59 pm
hunchback (11/27/2013)
Your comment about the default for frame is not quiet right. If you specify the ORDER BY subclause but not frame in a function that can accept optional ROWS/RANGE specification then the default will be "RANGE UNBOUNDED PRECEDING AND CURRENT ROW" which have a different treatment if we have ties on the columns used in the ORDER BY subclause. You can corroborate this from BOL.
Edit: You're right. You do need the ROWS window frame in my solution.
My initial post misinterpreted what you were saying.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 27, 2013 at 6:02 pm
Hi
Can I put this one up 🙂
select personid, goaldate, goalstatus,
row_number() over (partition by personid, goalstatus, g order by goaldate) * GoalStatus ConsecutiveGoals
from (
select personid, goaldate, goalstatus,
row_number() over (partition by personid order by goaldate) -
row_number() over (partition by personid, goalstatus order by goaldate) G
from #goals
) g
order by personid, goaldate;
November 27, 2013 at 6:20 pm
mickyT (11/27/2013)
HiCan I put this one up 🙂
select personid, goaldate, goalstatus,row_number() over (partition by personid, goalstatus, g order by goaldate) * GoalStatus ConsecutiveGoals
from (
select personid, goaldate, goalstatus,
row_number() over (partition by personid order by goaldate) -
row_number() over (partition by personid, goalstatus order by goaldate) G
from #goals
) g
order by personid, goaldate;
Of course you can! Welcome to the party!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply