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: