January 6, 2016 at 11:27 pm
Comments posted to this topic are about the item The Lonely Count
January 6, 2016 at 11:28 pm
This was removed by the editor as SPAM
January 7, 2016 at 12:25 am
Easy one, thanks!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 7, 2016 at 1:05 am
Interestingly enough, the most recent docs for COUNT() states that the OVER() requires the ORDER BY clause, which is patently untrue, at least up to SQL 2012....
January 7, 2016 at 2:25 am
This was removed by the editor as SPAM
January 7, 2016 at 4:47 am
Rune Bivrin (1/7/2016)
Interestingly enough, the most recent docs for COUNT() states that the OVER() requires the ORDER BY clause, which is patently untrue, at least up to SQL 2012....
Good discovery! I have forwarded your comment to Microsoft - my experience is that this kind of mistakes are usually corrected pretty quickly.
Also note that the syntax diagram suggests that not only the ORDER BY clause, but also the OVER clause itself is mandatory (though the text of the article does state that it's optional).
January 7, 2016 at 4:55 am
I like the question, but there are two small caveats.
First is that I would have liked the answer to be phrased as "10 in all 10 rows" or something equivalent, to make clear that there is a difference between COUNT with empty OVER() clause and COUNT with no OVER clause at all (which would have returned just a single value 10 if the myid value itself were omitted from the SELECT and a GROUP BY were added).
Second is that the explanation wrongly states that without ORDER BY, the partition is "ordered in natural order, which is however SQL Server reads the data". This is not true. Without ORDER BY, the partition is unordered, and still considered as a SET. In an unordered partition, the aggregate function is applied to the whole partition, and that is why the COUNT result is 10 for each row.
In an ordered partition, the aggregate applies to the result of the ROW or RANGE specfication, which default to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. So if the explanation would be right about the partition being "ordered in natural order", then the query would have assigned the values 1 through 10 based on that "natural order".
January 7, 2016 at 5:02 am
Hugo Kornelis (1/7/2016)
Also note that the syntax diagram suggests that not only the ORDER BY clause, but also the OVER clause itself is mandatory (though the text of the article does state that it's optional).
Yeah, I noticed that too, but that was so preposterous I ignored it.
January 7, 2016 at 5:30 am
Useful and interesting question, thank you Steve.
The same result for i may give also this script:
SELECT
MyID
, COUNT(*) OVER (PARTITION BY '') AS i
-- OVER (PARTITION BY '') is the same as OVER () --
FROM dbo.MyTable;
January 7, 2016 at 5:47 am
Great (yet simple) illustration of a concept I use frequently. I did not know that the ORDER BY clause was optional, so I learned something new. Thanks.
January 7, 2016 at 6:27 am
Good, an easy one, thank you.
January 7, 2016 at 7:14 am
Ed Wagner (1/7/2016)
Great (yet simple) illustration of a concept I use frequently. I did not know that the ORDER BY clause was optional, so I learned something new. Thanks.
That's very important though!
If you specify an ORDER BY clause, you have a windowing function as introduced in SQL Server 2012. Which means you have the default of RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which is a terrible default performance wise, but it can also lead to unexpected results.
If you don't specify an ORDER BY clause, you have the "traditional" window function as defined in SQL 2005, which is useful for calculating subtotals and totals.
More info:
Beware the defaults! (in windowing functions)[/url]
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 7, 2016 at 7:21 am
Koen Verbeeck (1/7/2016)
Ed Wagner (1/7/2016)
Great (yet simple) illustration of a concept I use frequently. I did not know that the ORDER BY clause was optional, so I learned something new. Thanks.That's very important though!
If you specify an ORDER BY clause, you have a windowing function as introduced in SQL Server 2012. Which means you have the default of RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which is a terrible default performance wise, but it can also lead to unexpected results.
If you don't specify an ORDER BY clause, you have the "traditional" window function as defined in SQL 2005, which is useful for calculating subtotals and totals.
More info:
Right - the totals and subtotals is the exact scenario I was thinking of. I agree with your point about the default, but at least we have the flexibility. Personally, I think the windowing functions are one of the most useful things they've introduced in the past 10 years since SQL 2005. Just another tool in the toolbox, but a highly useful one.
January 7, 2016 at 11:32 am
Interesting question, interesting discussion. Thanks, Steve!
January 7, 2016 at 11:38 am
Ed Wagner (1/7/2016)
Koen Verbeeck (1/7/2016)
Ed Wagner (1/7/2016)
Great (yet simple) illustration of a concept I use frequently. I did not know that the ORDER BY clause was optional, so I learned something new. Thanks.That's very important though!
If you specify an ORDER BY clause, you have a windowing function as introduced in SQL Server 2012. Which means you have the default of RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which is a terrible default performance wise, but it can also lead to unexpected results.
If you don't specify an ORDER BY clause, you have the "traditional" window function as defined in SQL 2005, which is useful for calculating subtotals and totals.
More info:
Right - the totals and subtotals is the exact scenario I was thinking of. I agree with your point about the default, but at least we have the flexibility. Personally, I think the windowing functions are one of the most useful things they've introduced
in the past 10 yearssince SQL 2005. Just another tool in the toolbox, but a highly useful one.
They are immensely useful in writing BI queries. I can't imagine my job without window functions.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply