January 9, 2008 at 1:22 pm
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.
January 9, 2008 at 1:39 pm
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?
January 9, 2008 at 2:34 pm
In this example, I would like to pick the earliest quarter (i.e., 01 wins over 03).
January 9, 2008 at 7:13 pm
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?
July 2, 2010 at 10:15 am
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().:-)
July 6, 2010 at 3:26 am
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)
February 27, 2014 at 9:18 am
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