+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

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

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

    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



  2. Re: "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@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
    >
    >



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

    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?

  4. Re: "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?
    >


  5. Re: "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@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?
    >>



  6. Re: "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@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?
    > >>

    >
    >


  7. Re: "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.

  8. Re: "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.


  9. Re: "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.
    >
    >


  10. Re: "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@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.
    >>
    >>



+ Reply to Thread
Page 1 of 2 1 2 LastLast