Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2009
    Posts
    51

    Unanswered: SQL 2005 upwards for better.

    Hi All,

    select Table_1.*
    case
    when Table_1.Col1 = 'A' then '01'
    when Table_1.Col1 = 'C' then '02'
    when Table_1.col1 = 'B' then '03
    end as 'grouping_order'
    from Table_1
    order by Table1.grouping_order

    -----------------------------

    The above similar SQL worke for SQL2k
    but this fails from SQL2005 upwards.

    Question is this a bug in SQL2K fixed or is this a bug in SQL2005 upwards?

    Thanks
    TT

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No, there is a bug in SQL 2000 which sometimes allows code like that to work. SQL 2005 and later versions corrected the bug, so the code fails as the ISO standard requires it to fail.

    If you use the standard construct, it works in all cases but doesn't have the extra column named grouping_order:
    Code:
    SELECT Table_1.*
       FROM Table_1 
       ORDER BY
          CASE 
             WHEN Table_1.Col1 = 'A' then '01'
             WHEN Table_1.Col1 = 'C' then '02'
             WHEN Table_1.col1 = 'B' then '03
          END
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    2005 do allow the use of alias names from the select list
    The order by clause is applied last.
    Basically the order is to evaluate the From clause, then the Where clause, then group by, then having, then select clause and last the order by
    The problem is that you prefix it with the table name and that column does not exist in the table.
    You should use
    order by grouping_order

    PS. That also explain why you can't use an alias in the where clause as the select clause has not been evaluated yet.
    Last edited by pdreyer; 05-07-09 at 04:25.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Actually the problem is because the grouping_order is an expression, not a column. When the expression is used in the SELECT list, it isn't associated with any alias and is no longer promoted to the column list at all, which is exactly how it should work.

    By putting the expression where it is actually used (in the GROUP BY clause), it gets evaluated correctly and there is no confusion.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    I think you meant "in the order by clause"
    I agree there is no need to specify the expression in the select list
    However if you do, the alias is available for use in the order by clause e.g.
    Code:
    select Table_1.*
    ,case 
    when Table_1.Col1 = 'A' then '01'
    when Table_1.Col1 = 'C' then '02'
    when Table_1.col1 = 'B' then '03'
    end as 'grouping_order'
    from (select            -- test data
    1, 'A' union all select
    2, 'B' union all select
    3, 'C' union all select
    4, 'A' union all select
    5, 'B')Table_1(id,Col1) -- end test data
    order by grouping_order
    
    select @@version
    
    id          Col1 grouping_order
    ----------- ---- --------------
    1           A    01
    4           A    01
    3           C    02
    5           B    03
    2           B    03
    
    (5 row(s) affected)
    
    
    -------------------------
    Microsoft SQL Server 2005

  6. #6
    Join Date
    Feb 2009
    Posts
    51
    pdreyer and pat phelan

    thank you for the input.
    'sql 2000 still runs that query due to its incorrect ways of handling it'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •