Results 1 to 3 of 3

Thread: Order By IsNull

  1. #1
    Join Date
    Mar 2010
    Posts
    2

    Unanswered: Order By IsNull

    Hi

    I am experiencing something peculiar with a query that orders the result on a column enclosed by IsNull and the output is selected in a local @variable. Consider the following query:

    Code:
    --- Query Starts --
    Declare @t1 Table (SeqNo Int, ID Varchar(50))
    Insert Into @t1
    Select 1, 'One' Union
    Select 2, 'Two' Union
    Select Null, 'Unknown'
    
    Declare @t2 Table (SeqNo Int, ColName Varchar(50))
    Insert Into @t2
    Select 1, 'First' Union
    Select 2, 'Second' Union
    Select 3, 'Third'
    
    Select IsNull(t1.SeqNo,t2.SeqNo),t2.ColName  From
    @t2 t2
    Left Outer Join @t1 t1 On t2.SeqNo = t1.SeqNo
    Order By IsNull(t1.SeqNo,t2.SeqNo)
    
    Declare @csv Varchar(1000)
    Set @csv = ''
    
    Select @csv = @csv + ',' + t2.ColName  From
    @t2 t2
    Left Outer Join @t1 t1 On t2.SeqNo = t1.SeqNo
    Order By IsNull(t1.SeqNo,t2.SeqNo)
    
    Select @csv
    
    Set @csv = ''
    
    Select @csv = @csv + ',' + t2.ColName  From
    @t2 t2
    Left Outer Join @t1 t1 On t2.SeqNo = t1.SeqNo
    Order By t1.SeqNo
    
    Select @csv
    -- Query Ends --
    The above query produces three resultsets as:
    1 First
    2 Second
    3 Third
    ------------------------------
    ,Third
    ------------------------------
    ,Third,First,Second
    ------------------------------

    The output produced in second result is ',Third' whereas I expect it to be ',First,Second,Third'. The table output produced in first result is OK but when the same is selected in a @var only the last value is saved. To me it should be a straightforward output, but I don't know if I am missing something here !!

  2. #2
    Join Date
    Feb 2007
    Posts
    15
    Hello Umairm,

    On the second query just replace this
    Select @csv = @csv + ',' + t2.ColName From
    @t2 t2
    Left Outer Join @t1 t1 On t2.SeqNo = t1.SeqNo
    Order By IsNull(t1.SeqNo,t2.SeqNo)
    with this one
    Select @csv = @csv + ',' + t2.ColName From
    @t2 t2
    Left Outer Join @t1 t1 On t2.SeqNo = t1.SeqNo
    Order By t2.SeqNo
    the result set will be what you expect, i.e., ',First, Second, Third'

    Hope this help

    OS

  3. #3
    Join Date
    Mar 2010
    Posts
    2
    Thanks OS, you are right your suggested change will produce the result but not the required/correct result in every case. I tried to explain my issue through this example query, the query I provided is just part of the issue I faced in a project. However I progressed through a workaround but I am still eager to know the reason behind this.

Tags for this Thread

Posting Permissions

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