Q
Problem solve Get help with specific problems with your technologies, process and projects.

Why I love VIEWs

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. How can I select just 25 clients per agent?

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.

This was last published in July 2005

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close