Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Selecting first duplicate record Expand / Collapse
Author
Message
Posted Wednesday, January 9, 2008 1:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 9, 2008 5:34 PM
Points: 3, 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.

Post #440887
Posted Wednesday, January 9, 2008 1:39 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:46 PM
Points: 7,104, Visits: 15,425
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?
Post #440894
Posted Wednesday, January 9, 2008 2:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 9, 2008 5:34 PM
Points: 3, Visits: 7
In this example, I would like to pick the earliest quarter (i.e., 01 wins over 03).
Post #440914
Posted Wednesday, January 9, 2008 7:13 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:46 PM
Points: 7,104, Visits: 15,425
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?
Post #440967
Posted Friday, July 2, 2010 10:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 8, 2013 4:44 PM
Points: 5, 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 (;). and the ROW_NUMBER statement needs open and closed parentheses after it ROW_NUMBER().
Post #946973
Posted Tuesday, July 6, 2010 3:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 16, 2012 2:16 AM
Points: 11, 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)
Post #947780
Posted Thursday, February 27, 2014 9:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 10:21 AM
Points: 14, Visits: 124
Thanks MArk Miller #4. i appreciate your post
Post #1545960
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse