"order by" in SQL 2005 vs. SQL 2000 or other issue?

Sure. Thanks!

I am using temporary table as you see.
The problem is Manager is not ordered by LastName, FirstName.
Also the Total is shown up at first line rather than last line of the report.

--- 1) Manager List
SELECT am.sAMFirstName + ' ' + am.sAMLastName as 'sManager',
...INTO #Result1
FROM tableA AS f
LEFT JOIN tableB AS am
ON f.nEmployeeID = am.nManagerID
...GROUP BY f.nEmployeeID, am.sAMFirstName, am.sAMLastName, f.nReportCode
ORDER BY sManager, f.nReportCode

SELECT ..
INTO #Result2
FROM #Result1
GROUP BY sManager, nManagerID WITH CUBE

-- 2) Total
SELECT *
FROM #Result2
WHERE (nManagerID IS NOT NULL AND sManager IS NOT NULL AND sManager IS NOT
NULL) --these rows are totals for each manager/ managerid by report desc





"Stuart Ainsworth" wrote:

> On Aug 6, 2:41 pm, Justin Doh
> wrote:
> > I am trying to find any issue why the data is not coming out as it is intended.
> > I converted table and stored procedure from SQL 2000 to SQL 2005.
> > However, on several reports, data is not listed as it is intended by "order
> > by" clause.
> > I guess one cause might be "Ambiguous column" (which replaced by putting
> > alise in front of column name).
> >
> > Is there any other common factor why data is not coming out in the order as
> > I intended to be?
> >
> > Thanks in advance
>
> Can you give an example of a query that is not returning data as you'd
> expect?
>
 >> Stay informed about: ""order by"" in SQL 2005 vs. SQL 2000 or other issue? 

You are still writing BASIC (look at the data type prefixes!) and
thinking in sequential files (ORDER BY on a temp table you used to
fake a 1950's style scratch tape). An SQL programmer would have used
a derived table or CTE so the optimizer could work on the query.

Tables have no ordering; this concept is fundamental. You can use an
ORDER BY on an explicit or implicit cursor for display purposes.

>> Also the Total is shown up at first line rather than last line of the report. <<

Read the part in the manual about how to use the GROUPING() function
in the OLAP extensions to GROUP BY.
 >> Stay informed about: ""order by"" in SQL 2005 vs. SQL 2000 or other issue? 

Do you have ORDER BY in the query, or do you have ORDER BY in a view and you
are querying it without an outer ORDER BY? Without specific examples it's
going to be really hard to make any assessment and even harder to give you
any advice.




On 8/6/08 2:41 PM, in article
DC05029D-8937-49D9-813C-D16519F4989B.RemoveThis@microsoft.com, "Justin Doh"
wrote:

> I am trying to find any issue why the data is not coming out as it is
> intended.
> I converted table and stored procedure from SQL 2000 to SQL 2005.
> However, on several reports, data is not listed as it is intended by "order
> by" clause.
> I guess one cause might be "Ambiguous column" (which replaced by putting
> alise in front of column name).
>
> Is there any other common factor why data is not coming out in the order as
> I intended to be?
>
> Thanks in advance
>
>
 >> Stay informed about: ""order by"" in SQL 2005 vs. SQL 2000 or other issue? 

SELECT INTO with an ORDER BY doesn't really mean anything. A table is by
definition an unordered set of rows, so even with an ORDER BY, SQL Server is
free to insert those rows in any physical order it feels is best, and
likewise when you SELECT without ORDER BY, it can also return those rows to
you in any order it feels is best. Just because you observed a certain
behavior in SQL Server 2000 does not make the behavior documented or
guaranteed. You need to add an order by on your last SELECT * ...


On 8/6/08 3:25 PM, in article
A890C1F8-82B4-418B-82FD-F1FE88192260 DeleteThis @microsoft.com, "Justin Doh"
wrote:

> Sure. Thanks!
>
> I am using temporary table as you see.
> The problem is Manager is not ordered by LastName, FirstName.
> Also the Total is shown up at first line rather than last line of the report.
>
> --- 1) Manager List
> SELECT am.sAMFirstName + ' ' + am.sAMLastName as 'sManager',
> ..INTO #Result1
>
> FROM tableA AS f
> LEFT JOIN tableB AS am
> ON f.nEmployeeID = am.nManagerID
> ..GROUP BY f.nEmployeeID, am.sAMFirstName, am.sAMLastName, f.nReportCode
>
> ORDER BY sManager, f.nReportCode
>
> SELECT ..
> INTO #Result2
> FROM #Result1
> GROUP BY sManager, nManagerID WITH CUBE
>
> -- 2) Total
> SELECT *
> FROM #Result2
> WHERE (nManagerID IS NOT NULL AND sManager IS NOT NULL AND sManager IS NOT
> NULL) --these rows are totals for each manager/ managerid by report desc
>
>
>
>
>
> "Stuart Ainsworth" wrote:
>
>> On Aug 6, 2:41 pm, Justin Doh
>> wrote:
>>> I am trying to find any issue why the data is not coming out as it is
>>> intended.
>>> I converted table and stored procedure from SQL 2000 to SQL 2005.
>>> However, on several reports, data is not listed as it is intended by "order
>>> by" clause.
>>> I guess one cause might be "Ambiguous column" (which replaced by putting
>>> alise in front of column name).
>>>
>>> Is there any other common factor why data is not coming out in the order as
>>> I intended to be?
>>>
>>> Thanks in advance
>>
>> Can you give an example of a query that is not returning data as you'd
>> expect?
>>
 >> Stay informed about: ""order by"" in SQL 2005 vs. SQL 2000 or other issue? 

Hi Aaron,

I am little bit confused because I am not sure which one you are referring to.
It seems as you are referring to the last query.

I am concerned about the 1st query ( 1. Manager List) rather than the last
query.
There is "Order by" clause, isn't it?

Thanks.

"Aaron Bertrand [SQL Server MVP]" wrote:

> SELECT INTO with an ORDER BY doesn't really mean anything. A table is by
> definition an unordered set of rows, so even with an ORDER BY, SQL Server is
> free to insert those rows in any physical order it feels is best, and
> likewise when you SELECT without ORDER BY, it can also return those rows to
> you in any order it feels is best. Just because you observed a certain
> behavior in SQL Server 2000 does not make the behavior documented or
> guaranteed. You need to add an order by on your last SELECT * ...
>
>
> On 8/6/08 3:25 PM, in article
> A890C1F8-82B4-418B-82FD-F1FE88192260.TakeThisOut@microsoft.com, "Justin Doh"
> wrote:
>
> > Sure. Thanks!
> >
> > I am using temporary table as you see.
> > The problem is Manager is not ordered by LastName, FirstName.
> > Also the Total is shown up at first line rather than last line of the report.
> >
> > --- 1) Manager List
> > SELECT am.sAMFirstName + ' ' + am.sAMLastName as 'sManager',
> > ..INTO #Result1
> >
> > FROM tableA AS f
> > LEFT JOIN tableB AS am
> > ON f.nEmployeeID = am.nManagerID
> > ..GROUP BY f.nEmployeeID, am.sAMFirstName, am.sAMLastName, f.nReportCode
> >
> > ORDER BY sManager, f.nReportCode
> >
> > SELECT ..
> > INTO #Result2
> > FROM #Result1
> > GROUP BY sManager, nManagerID WITH CUBE
> >
> > -- 2) Total
> > SELECT *
> > FROM #Result2
> > WHERE (nManagerID IS NOT NULL AND sManager IS NOT NULL AND sManager IS NOT
> > NULL) --these rows are totals for each manager/ managerid by report desc
> >
> >
> >
> >
> >
> > "Stuart Ainsworth" wrote:
> >
> >> On Aug 6, 2:41 pm, Justin Doh
> >> wrote:
> >>> I am trying to find any issue why the data is not coming out as it is
> >>> intended.
> >>> I converted table and stored procedure from SQL 2000 to SQL 2005.
> >>> However, on several reports, data is not listed as it is intended by "order
> >>> by" clause.
> >>> I guess one cause might be "Ambiguous column" (which replaced by putting
> >>> alise in front of column name).
> >>>
> >>> Is there any other common factor why data is not coming out in the order as
> >>> I intended to be?
> >>>
> >>> Thanks in advance
> >>
> >> Can you give an example of a query that is not returning data as you'd
> >> expect?
> >>
>
>
 >> Stay informed about: ""order by"" in SQL 2005 vs. SQL 2000 or other issue? 

> I am little bit confused because I am not sure which one you are referring to.
> It seems as you are referring to the last query.
>
> I am concerned about the 1st query ( 1. Manager List) rather than the last
> query.
> There is "Order by" clause, isn't it?

Sure, there is an ORDER BY clause. But you are inserting rows into a NEW
table. A table is by definition an UNORDERED set of rows. So where do you
expect your order by clause to be respected, and how are you validating that
it wasn't? If you just say "SELECT * FROM newtable" SQL Server is free to
return those rows in any order it wants. This *might* be the same as the
order you thought you tried to insert, but it is not something you should
expect or rely on. If you want data returned out of the table in a specific
order, then specify an order by clause on the final select, not on an insert
or select into statement.
 >> Stay informed about: ""order by"" in SQL 2005 vs. SQL 2000 or other issue? 

You know what.
Your explanation is completely logical, and I did not pay close attention to
what you were trying to say at first.
I guess right now I am having difficulty correcting the last query in order
to get the data straight. Please refer to the bottom query as it is the
correct query.

SELECT *
FROM #Result2
WHERE
(nManagerID IS NOT NULL AND sManager IS NOT NULL AND sManager IS NOT
NULL)--these rows are totals for each manager/managerid by report desc
OR
(nManagerID IS NULL AND sManager IS NULL AND sManager IS NULL)--this row
contains report totals
-- ORDER BY sManager

If I add "ORDER BY sManager" at the end, it lists data aphabetically.
However, "report total" line is shown at the top rather the at the last
column.

How do I fix that?
Thanks in advance.





"Aaron Bertrand [SQL Server MVP]" wrote:

> > I am little bit confused because I am not sure which one you are referring to.
> > It seems as you are referring to the last query.
> >
> > I am concerned about the 1st query ( 1. Manager List) rather than the last
> > query.
> > There is "Order by" clause, isn't it?
>
> Sure, there is an ORDER BY clause. But you are inserting rows into a NEW
> table. A table is by definition an UNORDERED set of rows. So where do you
> expect your order by clause to be respected, and how are you validating that
> it wasn't? If you just say "SELECT * FROM newtable" SQL Server is free to
> return those rows in any order it wants. This *might* be the same as the
> order you thought you tried to insert, but it is not something you should
> expect or rely on. If you want data returned out of the table in a specific
> order, then specify an order by clause on the final select, not on an insert
> or select into statement.
>
>
 >> Stay informed about: ""order by"" in SQL 2005 vs. SQL 2000 or other issue? 

Can you show sample data and desired results? I can't write a query against
data I can't see.


On 8/6/08 6:05 PM, in article
DEAACFA8-33EC-42A9-ACD9-EAF516061F3E.DeleteThis@microsoft.com, "Justin Doh"
wrote:

> You know what.
> Your explanation is completely logical, and I did not pay close attention to
> what you were trying to say at first.
> I guess right now I am having difficulty correcting the last query in order
> to get the data straight. Please refer to the bottom query as it is the
> correct query.
>
> SELECT *
> FROM #Result2
> WHERE
> (nManagerID IS NOT NULL AND sManager IS NOT NULL AND sManager IS NOT
> NULL)--these rows are totals for each manager/managerid by report desc
> OR
> (nManagerID IS NULL AND sManager IS NULL AND sManager IS NULL)--this row
> contains report totals
> -- ORDER BY sManager
>
> If I add "ORDER BY sManager" at the end, it lists data aphabetically.
> However, "report total" line is shown at the top rather the at the last
> column.
>
> How do I fix that?
> Thanks in advance.
>
>
>
>
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
>>> I am little bit confused because I am not sure which one you are referring
>>> to.
>>> It seems as you are referring to the last query.
>>>
>>> I am concerned about the 1st query ( 1. Manager List) rather than the last
>>> query.
>>> There is "Order by" clause, isn't it?
>>
>> Sure, there is an ORDER BY clause. But you are inserting rows into a NEW
>> table. A table is by definition an UNORDERED set of rows. So where do you
>> expect your order by clause to be respected, and how are you validating that
>> it wasn't? If you just say "SELECT * FROM newtable" SQL Server is free to
>> return those rows in any order it wants. This *might* be the same as the
>> order you thought you tried to insert, but it is not something you should
>> expect or rely on. If you want data returned out of the table in a specific
>> order, then specify an order by clause on the final select, not on an insert
>> or select into statement.
>>
>>
 >> Stay informed about: ""order by"" in SQL 2005 vs. SQL 2000 or other issue? 

Basically,

My result is like:
Manager LA SF NY
--------------------------------------------
Grand Total 564 456 189
Aaron Page 2 5 0
Bill Boy 5 1 7
John Doh 1 11 23
Jane Smith 7 0 3
...
Wayne World 3 1 7

Rather it should be something like this:
Manager LA SF NY
--------------------------------------------
Aaron Page 2 5 0
Bill Boy 5 1 7
John Doh 1 11 23
Jane Smith 7 0 3
...
Wayne World 3 1 7
Grand Total 564 456 189

I think dealing with the temporary table is the one issue, but can we do
something at the last query to direct table to show data accordingly?

(Last query)
...
WHERE
(nManagerID IS NOT NULL AND sManager IS NOT NULL AND sManager IS NOT
NULL) --these rows are totals for each manager/managerid by report desc
OR
(nManagerID IS NULL AND sManager IS NULL AND sManager IS NULL) --this row
contains report totals
-- ORDER BY sManager

Thank you.

"Aaron Bertrand [SQL Server MVP]" wrote:

> Can you show sample data and desired results? I can't write a query against
> data I can't see.
>
>
> On 8/6/08 6:05 PM, in article
> DEAACFA8-33EC-42A9-ACD9-EAF516061F3E RemoveThis @microsoft.com, "Justin Doh"
> wrote:
>
> > You know what.
> > Your explanation is completely logical, and I did not pay close attention to
> > what you were trying to say at first.
> > I guess right now I am having difficulty correcting the last query in order
> > to get the data straight. Please refer to the bottom query as it is the
> > correct query.
> >
> > SELECT *
> > FROM #Result2
> > WHERE
> > (nManagerID IS NOT NULL AND sManager IS NOT NULL AND sManager IS NOT
> > NULL)--these rows are totals for each manager/managerid by report desc
> > OR
> > (nManagerID IS NULL AND sManager IS NULL AND sManager IS NULL)--this row
> > contains report totals
> > -- ORDER BY sManager
> >
> > If I add "ORDER BY sManager" at the end, it lists data aphabetically.
> > However, "report total" line is shown at the top rather the at the last
> > column.
> >
> > How do I fix that?
> > Thanks in advance.
> >
> >
> >
> >
> >
> > "Aaron Bertrand [SQL Server MVP]" wrote:
> >
> >>> I am little bit confused because I am not sure which one you are referring
> >>> to.
> >>> It seems as you are referring to the last query.
> >>>
> >>> I am concerned about the 1st query ( 1. Manager List) rather than the last
> >>> query.
> >>> There is "Order by" clause, isn't it?
> >>
> >> Sure, there is an ORDER BY clause. But you are inserting rows into a NEW
> >> table. A table is by definition an UNORDERED set of rows. So where do you
> >> expect your order by clause to be respected, and how are you validating that
> >> it wasn't? If you just say "SELECT * FROM newtable" SQL Server is free to
> >> return those rows in any order it wants. This *might* be the same as the
> >> order you thought you tried to insert, but it is not something you should
> >> expect or rely on. If you want data returned out of the table in a specific
> >> order, then specify an order by clause on the final select, not on an insert
> >> or select into statement.
> >>
> >>
>
>
 >> Stay informed about: ""order by"" in SQL 2005 vs. SQL 2000 or other issue? 

ORDER BY CASE WHEN nManagerID IS NULL AND sManager IS NULL
--AND sManager IS NULL --redundant, no?
THEN 1 ELSE 0 END, sManager



On 8/6/08 6:59 PM, in article
0924C99B-8C16-456F-9130-0487EBEAFB87 RemoveThis @microsoft.com, "Justin Doh"
wrote:

> Basically,
>
> My result is like:
> Manager LA SF NY
> --------------------------------------------
> Grand Total 564 456 189
> Aaron Page 2 5 0
> Bill Boy 5 1 7
> John Doh 1 11 23
> Jane Smith 7 0 3
> ..
>
> Wayne World 3 1 7
>
> Rather it should be something like this:
> Manager LA SF NY
> --------------------------------------------
> Aaron Page 2 5 0
> Bill Boy 5 1 7
> John Doh 1 11 23
> Jane Smith 7 0 3
> ..
>
> Wayne World 3 1 7
> Grand Total 564 456 189
>
> I think dealing with the temporary table is the one issue, but can we do
> something at the last query to direct table to show data accordingly?
>
> (Last query)
> ..
>
> WHERE
> (nManagerID IS NOT NULL AND sManager IS NOT NULL AND sManager IS NOT
> NULL) --these rows are totals for each manager/managerid by report desc
> OR
> (nManagerID IS NULL AND sManager IS NULL AND sManager IS NULL) --this row
> contains report totals
> -- ORDER BY sManager
>
> Thank you.
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
>> Can you show sample data and desired results? I can't write a query against
>> data I can't see.
>>
>>
>> On 8/6/08 6:05 PM, in article
>> DEAACFA8-33EC-42A9-ACD9-EAF516061F3E RemoveThis @microsoft.com, "Justin Doh"
>> wrote:
>>
>>> You know what.
>>> Your explanation is completely logical, and I did not pay close attention to
>>> what you were trying to say at first.
>>> I guess right now I am having difficulty correcting the last query in order
>>> to get the data straight. Please refer to the bottom query as it is the
>>> correct query.
>>>
>>> SELECT *
>>> FROM #Result2
>>> WHERE
>>> (nManagerID IS NOT NULL AND sManager IS NOT NULL AND sManager IS NOT
>>> NULL)--these rows are totals for each manager/managerid by report desc
>>> OR
>>> (nManagerID IS NULL AND sManager IS NULL AND sManager IS NULL)--this row
>>> contains report totals
>>> -- ORDER BY sManager
>>>
>>> If I add "ORDER BY sManager" at the end, it lists data aphabetically.
>>> However, "report total" line is shown at the top rather the at the last
>>> column.
>>>
>>> How do I fix that?
>>> Thanks in advance.
>>>
>>>
>>>
>>>
>>>
>>> "Aaron Bertrand [SQL Server MVP]" wrote:
>>>
>>>>> I am little bit confused because I am not sure which one you are referring
>>>>> to.
>>>>> It seems as you are referring to the last query.
>>>>>
>>>>> I am concerned about the 1st query ( 1. Manager List) rather than the last
>>>>> query.
>>>>> There is "Order by" clause, isn't it?
>>>>
>>>> Sure, there is an ORDER BY clause. But you are inserting rows into a NEW
>>>> table. A table is by definition an UNORDERED set of rows. So where do you
>>>> expect your order by clause to be respected, and how are you validating
>>>> that
>>>> it wasn't? If you just say "SELECT * FROM newtable" SQL Server is free to
>>>> return those rows in any order it wants. This *might* be the same as the
>>>> order you thought you tried to insert, but it is not something you should
>>>> expect or rely on. If you want data returned out of the table in a
>>>> specific
>>>> order, then specify an order by clause on the final select, not on an
>>>> insert
>>>> or select into statement.
>>>>
>>>>
>>
>>
 >> Stay informed about: ""order by"" in SQL 2005 vs. SQL 2000 or other issue?