Query Problem

I am stuck with a query. May be I am missing something.Plz have a look...

CREATE TABLE [Order](
Orderid VARCHAR(10),
orderdate DATETIME)

CREATE TABLE Product(
Prodid VARCHAR(10),
ProdDes VARCHAR(20),
ProdPrice INT)

CREATE TABLE OrdDetails(
OrddetailsID VARCHAR(10),
OrdID VARCHAR(10),
ProdID VARCHAR(10))

INSERT INTO [Order] VALUES ('1','5/11/2006')
INSERT INTO [Order] VALUES ('2','5/11/2006')
INSERT INTO [Order] VALUES ('3','6/11/2006')

INSERT INTO Product VALUES ('1','TV',16)
INSERT INTO Product VALUES ('2','LCD',20)
INSERT INTO Product VALUES ('3','DVD',9)
INSERT INTO Product VALUES ('4','MP',19)

INSERT INTO OrdDetails VALUES ('1','1','1')
INSERT INTO OrdDetails VALUES ('2','1','2')
INSERT INTO OrdDetails VALUES ('3','1','3')
INSERT INTO OrdDetails VALUES ('4','2','2')
INSERT INTO OrdDetails VALUES ('5','2','4')
INSERT INTO OrdDetails VALUES ('6','3','2')

--Query

select dd.orderdate,

max(dd.totprice)

FROM

(select TOP 100 PERCENT dbo.[Order].orderdate,
dbo.[Order].Orderid,
SUM(dbo. Product .ProdPrice) AS TotPrice
FROM dbo.OrdDetails INNER JOIN
dbo.[Order] ON dbo.OrdDetails.OrdID = dbo.[Order].Orderid INNER JOIN
dbo. Product ON dbo.OrdDetails.ProdID = dbo. Product .Prodid
GROUP BY dbo.[Order].orderdate, dbo.[Order].Orderid
ORDER BY dbo.[Order].orderdate, dbo.[Order].Orderid)dd
group by dd.orderdate

--Query
Drop table [Order]
Drop Table Product
Drop Table OrdDetails

The output needed is maximum summation of the order no ,I mean group by orderdate then orderno

Orderdate Ordernumber
5 nov 1
6 nov 3

at this momnet I am getting the orderdate and price
Plz help
Thanks!!"maximum summation of the order no" ???|||"maximum summation of the order no" ???
Thanks Rudy,
The requirement is -
Date Orderid Sum(price)

5/11/2006 1 41
5/11/2006 2 39
6/11/2006 3 20

Now in each date we need to pick the highest sum(price) with respective to orderid
So the final ouptput should be
Orderdate Orderid
5/11/2006 1
6/11/2006 3
I think now its a bit more clear...|||select O.orderdate
, O.Orderid
, sum(P.ProdPrice) AS TotPrice
from dbo.[Order] as O
inner
join dbo.OrdDetails as OD
on OD.OrdID = O.Orderid
inner
join dbo.Product as P
on P.Prodid = OD.ProdID
group
by O.orderdate
, O.Orderid
having sum(P.ProdPrice) =
( select max(TP)
from (
select sum(dbo.Product.ProdPrice) AS TP
from dbo.[Order]
inner
join dbo.OrdDetails
on dbo.OrdDetails.OrdID
= dbo.[Order].Orderid
inner
join dbo.Product
on dbo.Product.Prodid
= dbo.OrdDetails.ProdID
where dbo.[Order].orderdate
= O.orderdate
group
by dbo.[Order].Orderid
) as same_day_orders
)
order
by O.orderdateresults:

2006-05-11 00:00:00 1 45
2006-06-11 00:00:00 3 20|||So what you really want is to find the orderid and orderdate for the order with the largest total price for a given day? If that is the case, then the best way that I know to get that answer is in stages, something like:CREATE TABLE #ordersByDate (
orderdate DATETIME
, orderid INT
, TotPrice INT
)

INSERT INTO #ordersByDate (
orderdate, orderid, TotPrice
) SELECT Convert(DATETIME, Convert(CHAR(10), o.orderdate, 121)) AS orderdate
, o.orderid
, Sum(p.ProdPrice) AS TotPrice
FROM dbo.[Order] AS o
INNER JOiN dbo.ordDetails AS od
ON (od.ordID = o.orderid)
INNER JOIN dbo.Product AS p
ON (p.prodId = od.ProdId)
GROUP BY o.orderdate, o.orderid

SELECT orderdate, orderid
FROM #ordersByDate AS obd
WHERE obd.TotPrice = (SELECT Max(z1.TotPrice)
FROM #ordersByDate AS z1
WHERE z1.orderdate = obd.orderdate)
ORDER BY obd.orderdate, obd.orderid

DROP TABLE #ordersByDateThe problem is that this kind of question "pokes at the seams" of the SQL Engine. Because this kind of query requires careful sequencing of query evaluation in order to determine what occurs where/when, and SQL (like most query languages) doesn't offer explicit control of sub-expression evaluation. That's actually a blessing, because you get really complicated really fast when you try to handle things like that in a language, and simply making sequential steps is easy to write, read, and understand.

I build a temp table, and populate it very similar to your derived table dd. Once I've populated that, I make a two stage pass through it to compare this row with the largest order for this row's date, and only return this row in the result set if it is the largest. Just a word of warning, this code does NOT eliminate ties, so it is possible to get multiple rows returned for a given date.

R937's solution is elegant, and it is a single SQL operation, but for large result sets I think it will be rather slow because of the work that it needs to do to generate every row.

-PatP|||Thank you Great Guys!! :) :)
Thanks for those wonderful solutions...
Wishing you both A very HAPPY NEW YEAR 2007!!
And wish you all a wonderful and prosperous New Year 2007.
Enjoy !!!
:beer:

Query Merging/ Query Transformation ??

Hello,

Can someone plz refer/recommend any document on query merging? I am working on a database sever. The response time, of view's query has become a challange to me. I have tried everything, the last hope left is query merging.
But I didnt find any docs/papers/books on it.

Plz help.
Shigs.
=============================
Are there those,
In this world of brave,
Who can tell me,
How should I behave,
When I am disgraced.
=============================Could you please explain on the term Query merging.

If you want to merge certain table in the query you can use JOINs, refer to books online for more information.|||Could you please explain on the term Query merging.

If you want to merge certain table in the query you can use JOINs, refer to books online for more information.|||Hi Satya

Thanks a lot. Query Merging that I referred was not related to merging of one table's data into another, but if I fire a query, which is containing a subquery in it, then the execution may take two different strategies, 1. Execute the subquery, fetch the result and compare this data with the parent querie's data. 2. Merge the subquery into Parent query and execute them as if the query wasn't subquery but a single level '0' query.

The best xample is Oracle's COMPLEX_QUERY_MERGING option. I want to understand, how Oarcle internally merges these queries? Is there any Optimizer Doc, which gives the detail?

Waiting...|||Is your question in regards to Oracle? This is a SQL Server forum, and SQL Server generally does a good job of optimizing queries, even with complex embedded subqueries.
If you are having slow response times on SQL Server, then post your query and someone on the forum might be able to help you optimize it.|||You can check the execution plan of that query in query analyzer which gives you better idea about the table scans and performance.|||I have a question...are there fewer and fewer Oracle projects out there these days?|||One can only hope...