Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    12

    Unanswered: 'Order by' clause work incorrect

    when i try the following SQL batch, I get a result-set which is not order by
    datetime column 'out_date',but if I delete clause INTO #fifo_temp, I get a correct result with correct order.

    who can help me?thanks in advance
    ...
    select tag,stuff_id,stuff_name,cast(out_id as char(10)) as out_id,out_number,out_date,out_qty,remark
    INTO #fifo_temp from ##stuff_fifo UNION
    select tag,stuff_id,stuff_name,out_id,null,out_date,quant ity,remark
    from acc_cost.dbo.stuff_out where tag='A' and left(out_id,3) in ('XSA','TAP')
    ORDER BY out_date

    DROP TABLE ##stuff_fifo
    select * from #fifo_temp

    the following can get a correct result:

    select tag,stuff_id,stuff_name,cast(out_id as char(10)) as out_id,out_number,out_date,out_qty,remark
    from ##stuff_fifo UNION
    select tag,stuff_id,stuff_name,out_id,null,out_date,quant ity,remark
    from acc_cost.dbo.stuff_out where tag='A' and left(out_id,3) in ('XSA','TAP')
    ORDER BY out_date

  2. #2
    Join Date
    Feb 2004
    Location
    Hamburg, Germany
    Posts
    22
    If I am not mistaken, you have no influence on the physical order of recordsets saved in tables in MSSQL, so your table #fifo_temp will not be ordered by out_date.

    Choose the order of recordsets when extracting the data from the table, so use:

    select tag,stuff_id,stuff_name,cast(out_id as char(10)) as out_id,out_number,out_date,out_qty,remark
    INTO #fifo_temp from ##stuff_fifo UNION
    select tag,stuff_id,stuff_name,out_id,null,out_date,quant ity,remark
    from acc_cost.dbo.stuff_out where tag='A' and left(out_id,3) in ('XSA','TAP')

    DROP TABLE ##stuff_fifo
    select * from #fifo_temp
    ORDER BY out_date

    Regards

    kbk

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    KBK is correct. A table has no inherant "order" for either columns or rows, although a result set has order for both. The only place where order makes any sense (or difference) is in the result set.

    -PatP

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Set Out_Date as your clustered index and the data will be ordered the way you want, but if the order is important to you then it is best to specify it each time you select from the dataset using and ORDER BY clause.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Apr 2004
    Posts
    12
    I see the problem.Thank all kindly friends.

Posting Permissions

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