Selecting first duplicate record

  • I working on a SQL script that retrieves client info

    Last Name

    First Name

    Sales Rep

    Quarter

    When I run the script, I get a list of clients. What I need to do is pick the first record from the same client in cases where the sales rep and quarter column values are different. For instance

    Jones

    Karla

    Steve

    01

    Jones

    Karla

    Howard

    03

    How do I pick the first record and not have the second record show up in the results?

    Any help would be appreciated.

  • In order to make the results consistent, we'd need something reliable to tell us which of the two records is "first". Is there something like an identifier column that would tell which record was "first"? Or - a data added? or - does the quarter number win over the salesrep name?

    Without a predictable order (inside of the PARTITITION), the following syntax will return ONE row per client, but it might not be the same one each time you run it...

    WITH FirstClient (Last,first,rep,qtr,rn) as

    (

    select

    LastName as Last,

    FirstName as First,

    SalesRep as Rep,

    Quarter as Qtr,

    ROW_NUMBER OVER (PARTITION by LastName,FirstName ORDER by

    LastName,FirstName,Quarter,SalesRep) as rn

    from MyClientTable

    )

    select

    last,

    first,

    rep,

    qtr

    from FirstClient

    where rn=1

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • In this example, I would like to pick the earliest quarter (i.e., 01 wins over 03).

  • That's essentially how I coded it (after which a tie on quarters, it goes to the rep with the "lowest name" alphabetically).

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Still useful 2-years later! A couple of notes. If used in a longer queary the statement before WITH needs to end with a semi-colon (;). and the ROW_NUMBER statement needs open and closed parentheses after it ROW_NUMBER().:-)

  • Hi,

    If u want first client name details then use rowcount option.

    Rowcount keyword is inform to the sql server fetch the mensioned records from table.

    Ex:

    set rowcount 1

    select

    LastName,

    FirstName,

    SalesRep,

    Quarter

    from MyClientTable

    order by LastName,FirstName,SalesRep,Quarter

    set rowcount 0

    Thanks & Regards,

    G.Sethuraj (Sivakasi, TamilNadu,India)

  • Thanks MArk Miller #4. i appreciate your post

Viewing 7 posts - 1 through 6 (of 6 total)

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