Select query help

  • I have a table Customer which has following columns

    Custid,PersonId,Startdate,Enddate,name,age .......

    For each person there can be multiple rows in the table customer depending

    on startdate and end date .I want to select one personid for each person from table customer,the one having maximum end date.

    Consider the following example for table Customer

    CustIdPersonIdstartdate Enddate Nameage ...

    100101/12/201102/12/2011person1

    101102/13/201104/14/2011person1

    102202/11/201104/14/2011person2

    103204/15/201105/12/2011person2

    My query should give me

    101102/13/201104/14/2011person1

    103204/15/201105/12/2011person2

    Please provide some help as to how to form the query for such a scenario

    Thanks in advance

  • Something like this should work nicely

    Select

    t.CustId

    ,t.PersonId

    ,t.startdate

    ,t.Enddate

    ,t.Name

    from

    (

    Select

    CustId

    ,PersonId

    ,startdate

    ,Enddate

    ,Name

    ,ROW_NUMBER() OVER(PARTITION BY PersonId ORDER BY Enddate DESC) as rownum

    from

    table1

    ) as t

    where

    t.rownum=1

    Dan

    If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

  • Another is to do an INNER JOIN to a subquery for the CustID and MAX(EndDate), joined on both fields.

    A third method would be to put the above subquery into a CTE and join to the CTE.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Thanks a lot

  • It looks to me like Customer and Person data need to be separated in this, to make it work well.

    I'd be willing to be that "name" and "age" really belong in a "People" table.

    Normalizing the data that way will make everything you do with it easier.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

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

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