Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    2

    Unanswered: Placement of Null in result set - sort order

    Hi All,
    This is a really crappy question that I found a responce to ages ago, but cannot find on the forum just now.

    Whe I sort by a column that has null, then the NULL values are either in the top or bottom of the result set depending on whether the order by is asc or desc.

    If I have a table
    t with a column c which has 5 rows with values 1,2,3,4,NULL
    ************************************

    create table #t(c int)

    insert into #t values(1)
    insert into #t values(2)
    insert into #t values(3)
    insert into #t values(4)
    insert into #t values(null)

    *************************
    Then......
    select *
    from #t order by 1 asc
    results in .....
    NULL
    1
    2
    3
    4

    *******************
    and....
    select *
    from #t order by 1 desc
    results in.,...
    4
    3
    2
    1
    NULL


    I need null always at the end. so that I either get
    1
    2
    3
    4
    NULL

    or

    4
    3
    2
    1
    NULL


    Any ideas?

    Peter

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Placement of Null in result set - sort order

    You have this problem only with ASC. In this case, order like this:

    order by isnull(c, 2^31 - 1 ) ASC

    instead of

    order by 1 ASC
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Sep 2003
    Posts
    2
    Yo,
    why are you using a bit operator.
    can't I just use....
    select *
    from #t order by isnull(c, 4000 )

  4. #4
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Actually, it isn't a bit operator, but the Power operator. Anything is fine which is larger than your largest number to be expected of c.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

Posting Permissions

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