November 22, 2011 at 3:15 pm
Thanks for the question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 22, 2011 at 6:28 pm
L' Eomot Inversé (11/22/2011)
Nice question, but only works for non-empty tables.The reason it doesn't work for empty tables is an excellent illustration of SQL's idiotic confustion between emptiness and nullity - only a committee of mathematical illiterates could have decided that teh sum of the empty set should not be zero.
Strictly, non-empty sets rather than tables. More confusing examples, for fun:
-- 0
SELECT COUNT_BIG(*) FROM AdventureWorks.Production.Product AS p WHERE p.ProductID < 0
-- NULL
SELECT SUM(1) FROM AdventureWorks.Production.Product AS p WHERE p.ProductID < 0
-- NULL
SELECT MAX(X.x) FROM (SELECT ROW_NUMBER() OVER (ORDER BY p.ProductID) FROM AdventureWorks.Production.Product AS p WHERE p.ProductID < 0) AS X (x)
-- No result
SELECT MAX(X.x) OVER () FROM (SELECT ROW_NUMBER() OVER (ORDER BY p.ProductID) FROM AdventureWorks.Production.Product AS p WHERE p.ProductID < 0) AS X (x)
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 23, 2011 at 12:44 am
Nice questions. Answer could have been "Almost the same thing as Count(*)" instead of "The same thing as Count(*)".
November 23, 2011 at 6:15 am
SQL Kiwi (11/22/2011)
L' Eomot Inversé (11/22/2011)
Nice question, but only works for non-empty tables.The reason it doesn't work for empty tables is an excellent illustration of SQL's idiotic confustion between emptiness and nullity - only a committee of mathematical illiterates could have decided that teh sum of the empty set should not be zero.
Strictly, non-empty sets rather than tables. More confusing examples, for fun:
Yes, you are right, I should have said sets (or preferably bags) not tables.
And your four queries are a nice illustration of where things in SQL are wrong, and where they are right.
Of course I think the first in your collection is good mathematics and second is bad mathematics. The third is pretty straightforward and seems sensible (no error of mathematics there, sensible use of NULL.
The fourth is more interesting - I had to think about it for a bit - but I feel it is correct in mathematical terms. That's because I guess that that "OVER ()" means "partition into as many partitions as there are (not necessarily distinct) rows with each partition containing all the rows" (actually the exact meaning doesn't matter here - just that it's asking for some partition to be done - but it might matter in other queries, so I guess I should go and check that whether it really does mean that) so we are asking for a rowset containing the maxima of an empty set of rowsets (because there are no rows in the rowset being partitioned) so the result is an empty rowset.
Tom
November 23, 2011 at 9:27 am
Tom, I love your new tag line.
November 23, 2011 at 5:26 pm
Revenant (11/23/2011)
Tom, I love your new tag line.
I love it too - but there may be otheres (perhaps of a different gender persuasion) who don't like it, so I'm keeping my head down.
Tom
November 28, 2011 at 8:37 am
Thank you for the additional info, Paul and Tom.
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
December 1, 2011 at 6:03 am
Good easy question, thanks.
http://brittcluff.blogspot.com/
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply