|
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.
|
