SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru hifigrafix's Avatar
    Join Date
    Oct 2005
    Location
    Nashville, TN
    Posts
    628
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ORDER BY - IF Not Null

    Greetings,


    I am wanting to create a query that will have 2 order by parameters:

    SELECT * FROM table ORDER BY field2, field1

    My problem is I only want field 2 to be first in order if it exists (ie: I don't want NULL records indexed first).

    My example: First Name, Maiden Name, Last Name

    SELECT * FROM names ORDER BY middle_name, last_name;

    This way if someone had a maiden name of "anderson" and their last name was "johnson" they would show up with the "A's" and not the "J's".

    Thanks for any help.

    jw

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    ORDER BY coalesce(field2,field1), field1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jun 2008
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi - this code did not work for me.

    I have three fields, FirstName, MaidenName and LastName.

    Let's say I have three records with data in the above order:

    Allison Button Roberts
    Paul Roberts
    Sara Jones Smith

    The order should be:

    Allison Button Roberts
    Sara Jones Smith
    Paul Roberts

    because Button comes before Jones, and Roberts comes after Jones.

    Essentially, I only want to sort on the Maiden Name field if it is not null. Otherwise, I want to sort by the Last Name field.

    The above code: coalesce(field2,field1), field1
    sorts the above data like the following if field2 = Maiden Name and field1 = Last Name:

    Paul Roberts
    Allison Button Roberts
    Sara Jones Smith

    It looks as though it's sorting the rows where maiden name is not null together, and putting them after those rows where maiden name is null.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ChicagoRed View Post
    The above code...
    sorts the above data like the following...
    no, it doesn't
    Code:
    CREATE TABLE names
    ( firstname  VARCHAR(37)
    , maidenname VARCHAR(37)
    , lastname   VARCHAR(37)
    );
    INSERT INTO names VALUES
     ( 'Paul',    NULL,     'Roberts' )
    ,( 'Allison', 'Button', 'Roberts' )
    ,( 'Sara',    'Jones',  'Smith'   )
    ; 
    SELECT * FROM names
    ORDER BY COALESCE(maidenname,lastname),firstname
    ;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Jun 2008
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The example you gave was (field2,field1),field1 originally, but your latest example has (field2,field1),field3 --> (maidenname,lastname),firstname.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yeah, my bad, sorry

    SELECT * FROM names
    ORDER BY COALESCE(maidenname,lastname),firstname

    produces:

    Allison Button Roberts
    Sara Jones Smith
    Paul Roberts

    whereas

    SELECT * FROM names
    ORDER BY COALESCE(maidenname,lastname),lastname

    produces:

    Allison Button Roberts
    Sara Jones Smith
    Paul Roberts
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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