problems with the subqueries

I am a beginner in learning sql server 2000 and i wish to know the query for choosing

for (eg ) From "Order details" table in Northwind database

I wish the select the orderid with maximum number products

(i.e.,) say orderid 10250 contains maximum of 25 products with product ids 14, 15, 16... and so on

and the output should be of the form

Orderid Max_No_prod

10250 25

can anyone help me with this....?

From what I understand you want the order that has the most products. Here is the query:

Code Snippet

Select Top 1 OrderID, Count(Distinct ProductID) As Max_No_Prod From [Order Details]

Group By OrderID

Order By Max_No_Prod Desc

I hope this answers your question.

Best regards,

Sami Samir

|||

Hi,

Not sure I completely understand your question, this will return the largest volume of any one product in an order.

Code Snippet

SELECT OrderID, max(Quantity) As Max_No_prod

FROM [Order Details]

WHERE OrderID = 10250

GROUP BY OrderID

Jamie

sql