Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2003
    Posts
    6

    Question Unanswered: How to ORDER this secuence??

    Hello.

    I would like to order the secuence of numbers:
    Name | Age
    -----------------
    John 25
    Mary 24
    Silvia -->Null value
    Rachel 19


    and i would like the next solution:
    Name | Age
    -----------------
    Rachel 19 --> Low value
    Mary 24
    John 25 --> High value
    Silvia ---> Null value

    in other word I like that the null values will be at the end
    of the query. Is it possible?

    thanks.

  2. #2
    Join Date
    Sep 2003
    Posts
    522
    select * from your_table
    order by case when age is null then 999 else age end

  3. #3
    Join Date
    Oct 2003
    Posts
    6

    Lightbulb

    ORDER BY IIF(ISNULL(age), 999, age) ASC


    thanks.

  4. #4
    Join Date
    Sep 2003
    Posts
    522
    kiko123456 - yours will not fly, but the concept is the same.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ORDER BY ISNULL(age, 999) ASC

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, to be fair, this problem does apparently deal with people and their ages, and it's unlikely you'll ever get a person with an age higher than 999, but just the same, the method is flawed, and may fail if you were dealing with some other quantity where you couldn't be quite so sure that you were generating a "nothing-will-ever-be-higher-than-this" number

    so do it this way:

    select name, age
    from yourtable
    order by
    case when age is null then 1 else 0 end
    , age

    rudy
    http://r937.com/

  7. #7
    Join Date
    Sep 2003
    Posts
    522
    that's a good observation, except we were talking about people's age, not everything else. also, your case statement needs to be reversed, not when age is null then 1, but rather then 0 else 1.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, i did concede we were talking about age, and that 999 is likely good enough, my point was to develop better habits

    and when age is null, you do want 1, not 0, because that will sort the nulls last

    rudy

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Okay, how about:

    ORDER BY ISNUMERIC(age) desc, Age ASC

    Online help doesn't indicate what ISNUMERIC returns with a Null so I can't test this at the moment.

    blindman

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    isnull and isnumeric have the drawback that they aren't standard

    i still prefer

    order by case when age is null then 1 else 0 end, age

Posting Permissions

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