How to improve query

10 Feb 2006 10:59 PM

nashak

Following is a query based on Northwind. I need to output the highest
order value for each date.
The following works. However I want to see if there is any way to
remove the nested query and do some joins.

select a.orderid, a.orderdate, sum(b.quantity * b.unitprice) as total
from orders a, [order details] b
where a.orderid = b.orderid
group by a.orderid, a.orderdate
having sum(b.quantity * b.unitprice) = (select max(total1)
        from (select sum(quantity*unitprice) as total1, orders.orderdate as
date, orders.orderid
            from [order details], orders
            where [order details].orderid = orders.orderid
            group by orders.orderdate, orders.orderid
            ) as C
        where c.date = a.orderdate)
order by a.orderdate asc

Thanks,

11 Feb 2006 8:26 AM

Razvan Socol

Here is another way (but I'm not sure if it's better, though; in fact,
the execution plan indicates that it's worse):

select x.orderid, x.orderdate, x.total
from (
    select o1.orderid, o1.orderdate,
    sum(d1.quantity * d1.unitprice) as total
    from orders o1
    inner join [order details] d1 on o1.orderid = d1.orderid
    group by o1.orderid, o1.orderdate
) x inner join (
    select orderdate, max(total) as max_total
    from (
        select o2.orderid, o2.orderdate,
        sum(d2.quantity * d2.unitprice) as total
        from orders o2
        inner join [order details] d2 on o2.orderid = d2.orderid
        group by o2.orderid, o2.orderdate
    ) y
    group by orderdate
) z on x.orderdate=z.orderdate and x.total=z.max_total
order by x.orderdate

We can use a view (or a CTE in SQL Server 2005), instead of the x and y
derived tables, but this will not improve the performance (just the
readability).

Razvan