Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Accessing and changing data 2008 Expand / Collapse
Author
Message
Posted Monday, September 22, 2008 8:58 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:05 PM
Points: 5,472, Visits: 23,551
Comments posted to this topic are about the item Accessing and changing data 2008

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #574002
Posted Monday, September 22, 2008 10:31 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 5:31 PM
Points: 2,953, Visits: 435
Either I read the BOL totally wrong, or you got the answer totally wrong.

The BOL (linked in your answer) says they are equivalent. Unless "equivalent" does not equal to "the same"?

Specifying GROUPING SETS ( [,...n ]) as the GROUP BY list is equivalent to a UNION ALL of queries, each with one of the grouping sets as its GROUP BY list. Aggregates on floating-point numbers might return slightly different results.

The following statements are equivalent:

SELECT customer, year, SUM(sales)
FROM T
GROUP BY GROUPING SETS ((customer), (year))


SELECT customer, NULL as year, SUM(sales)
FROM T
GROUP BY customer
UNION ALL
SELECT NULL as customer, year, SUM(sales)
FROM T
GROUP BY year



Urbis, an urban transformation company
Post #574018
Posted Monday, September 22, 2008 11:35 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:40 AM
Points: 2,025, Visits: 445
And how do I know that "sales" is a floating-point numbers ?
i guess ?
Post #574036
Posted Tuesday, September 23, 2008 12:18 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 1:27 AM
Points: 2,366, Visits: 1,837
Hey i thought the answer is "They are the same".


"Keep Trying"
Post #574046
Posted Tuesday, September 23, 2008 1:12 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 2,078, Visits: 2,411
According to the link provided in the answer, the query with the GROUPING SETS equals the query with the GROUP BY and UNION ALL.
The correct answer of QotD should be "both queries are the same".


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #574078
Posted Tuesday, September 23, 2008 2:11 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, January 08, 2013 7:08 AM
Points: 469, Visits: 124
I thought "The following statements are equivalent" meant they were the same.

Silly me!
Post #574104
Posted Tuesday, September 23, 2008 2:22 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 11:04 AM
Points: 1,384, Visits: 1,240
Sorry to whine, but this question is a little disappointing - It's educational on at least two counts (grouping sets - new feature, and possible gotcha on floating point numbers), but ultimately what does it mean for two queries to be "the same"?

Do you judge based on the results? The query plan? The syntax?

Most people most of the time would be referring to "logical" equivalence - is the returned data set exactly the same regardless of the data set? And the answer to that question is, sadly, "It cannot be determined based on the information provided", as we would need to know the data structure of the table.

In case anyone reads this far, I do have a question: Do we know under what exact circumstances floating-point numbers (presumably Real and Float) will return different results? Can we assume Decimal types are exempt from this uncertainty?



http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
Post #574110
Posted Tuesday, September 23, 2008 2:51 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 09, 2013 3:40 AM
Points: 58, Visits: 153
liam.stirling (9/23/2008)
I thought "The following statements are equivalent" meant they were the same.

Silly me!


Exactly. Even though the resultsets might be slightly different, the statements are equivalent. Boooo, I want my point back! :)

Also does this highlight an issue with floating point anomaly in grouping sets, or has this always been an issue with aggregate statements on floating points, ie a SQL Server bug?

Post #574131
Posted Tuesday, September 23, 2008 3:16 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, January 08, 2013 7:08 AM
Points: 469, Visits: 124
Tao Klerks (9/23/2008)
Sorry to whine, but this question is a little disappointing - It's educational on at least two counts (grouping sets - new feature, and possible gotcha on floating point numbers), but ultimately what does it mean for two queries to be "the same"?

Do you judge based on the results? The query plan? The syntax?

Most people most of the time would be referring to "logical" equivalence - is the returned data set exactly the same regardless of the data set? And the answer to that question is, sadly, "It cannot be determined based on the information provided", as we would need to know the data structure of the table.

In case anyone reads this far, I do have a question: Do we know under what exact circumstances floating-point numbers (presumably Real and Float) will return different results? Can we assume Decimal types are exempt from this uncertainty?



I agree, Tao. There was an educational element to the question because I looked at something new, but there is also a semantic problem with the question, and even with reference to the BOL, I prefer your option of a third answer.
Post #574146
Posted Tuesday, September 23, 2008 4:07 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 30, 2011 3:44 AM
Points: 971, Visits: 62
I went to the link posted as an explanation [color=#00ffff]http://msdn.microsoft.com/en-us/library/bb510427.aspx[/color]
and the example listed at the top of the page is exactly the same as the question, and it says they are equivalent.

From the link above:

[color=#ff0000]The following statements are equivalent:
Copy Code

SELECT customer, year, SUM(sales)
FROM T
GROUP BY GROUPING SETS ((customer), (year))


Copy Code

SELECT customer, NULL as year, SUM(sales)
FROM T
GROUP BY customer
UNION ALL
SELECT NULL as customer, year, SUM(sales)
FROM T
GROUP BY year[/color]
Post #574176
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse