Same query fetching different results?

IIRC SQL does not guarantee any order except when you explicitely have an ORDER BY in place... sometimes different SQL Server versions/patchlevels "order" differently without an ORDER BY .

Up vote 0 down vote favorite share g+ share fb share tw.

I am following this article series to learn about ROLLUP and CUBE beyondrelational.com/modules/2/blogs/28/... For this query: select case when grouping(CustomerName) = 1 then 'All Customers' else CustomerName end as CustomerName, case when grouping(ItemName) = 1 then 'All Items' else ItemName end as ItemName, sum(quantity*pricepercase) as Amount1 from orders group by CustomerName, ItemName with cube The author has result like this: CustomerName ItemName Amount -------------------- -------------------- --------------------- Jacob Item 1 312.50 Jacob Item 2 480.00 Jacob All Items 799Mar 473 Mike Item 1 79Mar 477 Mike Item 2 49Mar 479 Mike All Items 119.00 All Customers All Items 9Mar 47 All Customers Item 1 38Mar 49 All Customers Item 2 529Mar 479 The two extra rows generated by cube are the last 2 rows. I get results like this: CustomerName ItemName Amount -------------------- -------------------- --------------------- Jacob Item 1 312.50 Mike Item 1 79Mar 477 All Customers Item 1 38Mar 49 Jacob Item 2 480.00 Mike Item 2 49Mar 479 All Customers Item 2 529Mar 479 All Customers All Items 9Mar 47 Jacob All Items 799Mar 473 Mike All Items 119.00 The first result set looks appropriate. Why is there a difference when I run it?

Sql-server tsql link|improve this question edited Mar 4 at 10:54 asked Mar 4 at 10:43Kishor Nanda9062717 95% accept rate.

Edited to include the query. – Kishor Nanda Mar 4 at 10:46 2 Don't use WITH CUBE msdn.microsoft.com/en-us/library/ms17767... Use the ISO compliant syntax for all new work. The non-ISO compliant syntax is provided for backward compatibility.

– ta.speot. Is Mar 4 at 10:58 Oh! That is good to know.

CUBE( elem1, elem2) instead of WITH CUBE – Kishor Nanda Mar 4 at 11:02.

IIRC SQL does not guarantee any order except when you explicitely have an ORDER BY in place... sometimes different SQL Server versions/patchlevels "order" differently without an ORDER BY. I don't know whether the author produced that result using SQL Server 2005 or 2008 or 2008 R2 etc. BUT I higghly suspect that this is the reason for what you see... IF you need a specific order in the resultset always use an explicit ORDER BYclause!

Ok, I suspected it could be that order is not guaranteed. However, if I do rollup instead of cube, I get the same order of results as the author. That is when I wondered if it could be something else.

As you said, the author may have used a different version of SQL Server. – Kishor Nanda Mar 4 at 10:59 If it turns out to be a case of different versions, is there a feature in the version that produces results in different order? – Kishor Nanda Mar 4 at 11:00 @KishorNanda any DB vendor is free to change the internal algorithms any way they want as long as the result is standards-conforming - this makes it hard to tell what feature might be related to such change... – Yahia Mar 4 at 11:05.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.