Using a derived table instead of a view

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

• Dozens more answers to tough SQL questions from Rudy Limeback.
• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.