SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Selecting first duplicate record


Selecting first duplicate record

Author
Message
Pablo Estes
Pablo Estes
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 7
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.
Matt Miller (4)
Matt Miller (4)
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15727 Visits: 18766
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?
Pablo Estes
Pablo Estes
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 7
In this example, I would like to pick the earliest quarter (i.e., 01 wins over 03).
Matt Miller (4)
Matt Miller (4)
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15727 Visits: 18766
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?
paul.vanhouse
paul.vanhouse
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 124
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 (Wink. and the ROW_NUMBER statement needs open and closed parentheses after it ROW_NUMBER().:-)
sethuraj, (From...
sethuraj, (From Sivakasi,TamilNadu,India)
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 57
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)
Leep 63464
Leep 63464
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 172
Thanks MArk Miller #4. i appreciate your post
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search