Q

Using a derived table instead of a view

I got some results from two tables (products, order_details) using a group by clause on prod_name:

prod_name   sum(quantity)
 apple       100
 Mango       200
 orange      400 

But what I want is the max of these sums (quantity), i.e orange 400. Can I get it in a single query statement? (I don't want to create a view and select the max value from it.)


Yes, you can do this with a derived table.

select prod_name
     , sumquantity  as maxsumquantity
  from (
       select prod_name
            , sum(quantity) as sumquantity
         from products
       inner
         join order_details
           on products.id
            = order_details.prod_id
       group
           by prod_name
       ) as dt 
 where sumquantity
     = (
       select max(sumquantity)
         from (
              select prod_name
                   , sum(quantity) as sumquantity
                from products
              inner
                join order_details
                  on products.id
                   = order_details.prod_id
              group
                  by prod_name
              ) as dt     
       ) 

Notice that you have to write the derived table twice. If you were to define a view, then the query would be:

select prod_name
     , sumquantity  as maxsumquantity
  from dtview
 where sumquantity
     = (
       select max(sumquantity)
         from dtview
       ) 

Maybe it's me, but I'd rather type the subquery which defines the derived table only once (to define a view), than type it every place it's needed.

On the other hand, there is often a simpler way to get what you want, depending on the specific requirements. In this situation, if you're using Microsoft SQL Server or Access, you can also get the answer you want like this:

select top 1
       prod_name
     , sum(quantity) as sumquantity
  from products
inner
  join order_details
    on products.id
     = order_details.prod_id
group
    by prod_name
order    
    by sumquantity desc

In MySQL, you'd use LIMIT instead of TOP.

For More Information


This was first published in July 2004
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close