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


    Requires Free Membership to View

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top