Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2012
    Posts
    9

    Unanswered: Multiple Address Types

    I have a list of email addresses with different types associated with them (i.e. 'Home', 'Work'). I am trying to write a query that will select the Home email address first if it exists and the Work address second if no home address exists.

    Sample Data

    #1 HOME person1@home.com
    #1 WORK person1@work.com
    #2 WORK person2@work.com
    #3 HOME person3@home.com
    #4 WORK person4@work.com

    Using this data I would want a list with Home addresses for person 1 and 3 and the work address for person 2 and 4.

    Thank you for helping.

  2. #2
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by pbooker View Post
    I have a list of email addresses with different types associated with them (i.e. 'Home', 'Work'). I am trying to write a query that will select the Home email address first if it exists and the Work address second if no home address exists.

    Sample Data

    #1 HOME person1@home.com
    #1 WORK person1@work.com
    #2 WORK person2@work.com
    #3 HOME person3@home.com
    #4 WORK person4@work.com

    Using this data I would want a list with Home addresses for person 1 and 3 and the work address for person 2 and 4.

    Thank you for helping.
    row_number() over (partition by a order by b) as rn
    --
    Lennart

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use Coalesce() to do this in most cases.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Nov 2012
    Posts
    9
    Thanks that worked the way I was wanting.

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    another way is to
    Code:
    order by case address_type
                     when 'home' then 1
                     when 'work' then 2
                     when 'other' then 3
                      else 4 end
    fetch first 1 row only

    Dave

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
  •