February 4, 2009 at 7:09 am
I am sure this task can be accomplished with many variations.
I run a call center that services existing customers.
Each Agent that answers phone calls are identified by an Agent ID Number (AgentId).
Each customer must have (and is identified by) an account number (AcctNo).
If I had a table that contained records of every phone contact for every agent & customer it would look like this (assume both are int datatypes):
AgentId AcctNo
--------- --------
658 686598
925 458962
453 126854
625 556255
925 556255
458 125893
An agent can talk to any customer so AgentId appears numerous times more than once.
A customer can call at any time so AcctNo appears numerous times more than once.
If I had to write a query that would extract the first 5 records for each and every agent for a sampling (being random is not an issue); how would I do it?
Many thanks.
February 4, 2009 at 7:44 am
Something like this would work, but there may be a better solution:
;WITH cteAgents AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY agentid ORDER BY agentid) AS row_id,
agentid,
AcctNo
FROM
dbo.calls
)
SELECT
*
FROM
cteAgents
WHERE
row_id <= 5
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 4, 2009 at 7:53 am
You can get the TOP 5 by using the TOP key word:
SELECT TOP 5...
However, you need to order by something. Do you also have a call date? If so, you can combine the TOP query with another query to return all the top 5's for any combination or simply return the top 5 for a given set of id's.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply