I have one table consisting of agents and their clients and policies. One agent can have more than one client, and one client can have more than one policy. The policy number is unique and the client belongs to one agent only. How can I select just 25 clients per agent? There are agent and client keys in addition to other client info.
Thanks for sending that in, that's a great question!
This type of query needs to be based on some sort of ranking or sequencing criterion. Which 25, is the question.
Okay, let's say we want the top 25 clients per agent, based on the highest total premium for all policies per client.
This means we need to sum up the policy premiums by client:
select agent_id , client_id , sum(premium) as total_prem from policies group by agent_id , client_id
So far, so good. Now, depending on which database we're using, we might be able to declare this query as a view, then we can use the view name in the FROM clause of the query we're going to build to select the top 25 clients per agent.
This is because we have to reference it twice. Using a view has appeal, as it makes for a more concise query.
Here's the query using derived tables, which are the inline subqueries in the FROM clause:
select t1.agent_id , t1.client_id , t1.total_prem from ( select agent_id , client_id , sum(premium) as total_prem from policies group by agent_id , client_id ) as t1 left outer join ( select agent_id , client_id , sum(premium) as total_prem from policies group by agent_id , client_id ) as t2 on t1.agent_id = t2.agent_id and t1.total_prem < t2.total_prem group by t1.agent_id , t1.client_id , t1.total_prem having count(*) < 25 order by t1.agent_id , t1.total_prem desc
Note that the two derived tables are identical. That's where we would have preferred to use a view name.
Here's the neat part. In the view, where we used
total_prem as the column alias, suppose we just call it
total without trying to say what kind of amount it is. Then the big outer query, where it references the derived table, or view, we would also have to change the column name.
Okay, let's do that and save the big outer query. Now edit the view, and instead of
sum(premium) as total, change this to
count(policyno) as total. The big outer query that you saved doesn't need changing, if you used a view. I think that's incredibly sexy.
P.S. Yes, you can use this idea in Microsoft Access. The view is a saved query; use the query name in the outer query's FROM clause, twice.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading