1

I have a scenario where I want to pull up to the first 500 rows for each agent_ID on a table. Example:

TABLE NAME: AGENT
Schema: AGENT_ID, CUST_ID, CUST_F_NAME, CUST_L_NAME
PK: CUST_ID

Scenario:

 AGENT_ID 1234 has 800 results AGENT_ID 4567 has 1000 results AGENT_ID 1212 has 300 results

I would like my results to pull only up to 500 records for each AGENT_ID. This table has hundreds of distinct AGENT_ID's.

I am having issues trying to write a SQL that will pull down this data and would like some help or suggestions.

Improve this question
2
  • You really named AGENT a table containing customers? Do you have a table containing agents? I mean 1 row per agent with agent_id primary key Commented Jun 12, 2014 at 16:19
  • That's not the real table name, just giving an example. 1 agent has multiple customers and on this table the customer has the agent_ID associated with it. I want to be able to pull down a list of the customer for each agent, but not have the results exceed 500 for each agent. There is a limitation on another system that creates a list from the results and that limits to 500 so I need to create another SQL to pull in rows 501 - 1000 as well. Commented Jun 12, 2014 at 16:25

1 Answer 1

Reset to default
3

You can use analytic function row_number() (or perhaps rank()) to do this:

select agent_id, cust_id, cust_f_name, cust_l_name from ( select agent_id, cust_id, cust_f_name, cust_l_name , row_number() over (partition by agent_id order by whatever) as rn from agent ) where rn <= 500;

Change whatever to a list of columns that gets you the data you want (latest 500, oldest 500 or whatever).

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.