Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Location
    California
    Posts
    40

    Unanswered: Sorting with NULLs

    Hi,

    I'm not sure why this is happening, but I'm trying to sort/order on this special field/column that I have created to allow precise ordering. However, for all of the values that are returned, if there are any NULLS, they are returned first/ahead of the values of what I want to be returned first. This field/column I created is called 'POW' (stands for Priority on Web). The values are just integers, 1-whatever, and we try to make sure that they're all unique. So for example, if we have a 1 in that field, then that row should be returned first, and if another row has 2, then it should be next. However, this is not working exactly - all of the NULL values are returned FIRST, then the proper values in the correct order. I want the NULLS to be last, because it may take some time for someone to go into each record, and assign it a unique value. My current SQL statement reads:

    mySQL = "SELECT SignTypes.Id, SignTypes.SignType, SignTypes.SignDescription, SignTypes.FinalSignWidth, SignTypes.FinalSignHeight FROM SignTypes JOIN PropLocSigns ON SignTypes.Id = PropLocSigns.SignTypeID WHERE PropLocSigns.PropLocID = " & Session("PropID") & " AND (RestrictOnWeb = 0 OR RestrictOnWeb IS NULL) ORDER BY SignTypes.POW; SELECT @@ROWCOUNT"

    So if someone knows of a way to put NULLS last, and still have my proper ordering, that would be great! I would think that there's some simple way of doing this, but not being an expert, I don't know. Thanks for the help!

  2. #2
    Join Date
    Oct 2003
    Posts
    706
    NULLs, being "the absence of any value at all" sort to the top. It may be that you want to omit those entirely with a WHERE .. IS NOT NULL clause. This is usually what you want to do.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  3. #3
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    And if that's not what you want to do (meaning the design is probably bad []), then you can use ISNULL(column_name,9999999) or something to force the sort order. Just know that the sort order will take longer.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ORDER BY case when SignTypes.POW is null then 1 else 0 end, SignTypes.POW
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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