How do I write this query?

  • gary.proctor

    Mr or Mrs. 500

    Points: 580

    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.

  • Jack Corbett

    SSC Guru

    Points: 184381

    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

  • Grant Fritchey

    SSC Guru

    Points: 396692

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 3 posts - 1 through 3 (of 3 total)

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