Post reply

Self Join

  • August 9, 2009 at 5:19 pm

    #71752

    I have a table of Phone Numbers. Employees may have any number of phones in this table Type 1 = Home, Type 2 = Cell.

    create table EmpPhone (EmployeeID char(6), PhoneType char(1), PhoneNumber char(10))

    insert table EmpPhone values ('230002','1','5551112222')

    insert table EmpPhone values ('230002','2','5552223333')

    insert table EmpPhone Values ('230002','2','5553334444')

    insert table EmpPhone values ('405000','1','5551234567')

    insert table EmpPhone values ('405000','1','5552345678')

    insert table EmpPhone values ('405000','2','5553214455')

    insert table EmpPhone values ('405000','2','5553215346')

    I would like to create a view that returns a single phone number for each EmployeeID and PhoneType. This would be the max(PhoneNumber) for each EmployeeID and PhoneType.

    For Example

    EmployeeID PhoneTYpe PhoneNumber

    230002 1 5551112222

    230002 2 5553334444

    405000 1 5552345678

    405000 2 5553215346

    I have seen this type of query before, but just cant seem to get it right.

    Thanks

  • August 9, 2009 at 7:37 pm

    #1036565

    Chuck Lucking (8/9/2009)

    This would be the max(PhoneNumber) for each EmployeeID and PhoneType.

    You just about wrote the query with that statement...

    SELECT EmployeeID, PhoneType, MAX(PhoneNumber) AS PhoneNumber

    FROM EmpPhone

    GROUP BY EmployeeID, PhoneType

    ORDER BY EmployeeID, PhoneType

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 11, 2009 at 8:15 am

    #1037450

    How is the max phone# determined .. do you have a date time stame for each phone type?

  • August 13, 2009 at 12:45 pm

    #1038923

    Thanks Jeff, I was hung up on having to do a self join with ranking to get the order I wanted. Just over engineering on my part. Thanks

    In answer to phone time stamp etc. What I am dealing with is a HR system that allows many phones of various types (cell, pager, home, work etc). This HR system feeds our legacy systems which only allow for 1 work and 1 cell phone. So the purpose is to consistently select the same phone to update the legacy system. Thus the reason for selecting the Max(phone number)

    Chuck

  • August 13, 2009 at 6:36 pm

    #1039077

    Chuck Lucking (8/13/2009)

    Thanks Jeff, I was hung up on having to do a self join with ranking to get the order I wanted. Just over engineering on my part. Thanks

    In answer to phone time stamp etc. What I am dealing with is a HR system that allows many phones of various types (cell, pager, home, work etc). This HR system feeds our legacy systems which only allow for 1 work and 1 cell phone. So the purpose is to consistently select the same phone to update the legacy system. Thus the reason for selecting the Max(phone number)

    Chuck

    Ah... got it. Thanks for taking the time, Chuck. I see a lot of "crazy" requests and I know there has to be a reason behind it all. Normally when I ask, people think I'm setting them up for a nasty lecture on normalization or something. That's not normally the case... Most of the time, I just want to know because I'm curious about the plight of my peers and sometimes I want to know because I might have a better way. Sadly, I don't have a better way for you this time... it's hard to fight a legacy when managment wants to keep the legacy alive. And, In this case, the changes would be probably be too costly and not enough value. (Damn... I hate it when I have to kowtow to that fact).

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply