SELECT Max( Count() ) ??

# Thread: SELECT Max( Count() ) ??

1. Senior Member
Join Date
Dec 1969
Posts
2,892

## SELECT Max( Count() ) ??

I have two related tables, &#039;Orders&#039; and &#039;OrderDetails.&#039; Each OrderID is a unique key in Orders, but a fk in OrderDetails.<BR><BR>Every fk_OrderID in OrderDetails can have many OrderDetailIDs. In order to build this part of my app, I need to find out what the highest amount of OrderDetailIDs are under any OrderID. I don&#039;t need to see the value for each OrderID, I just want to know what&#039;s the max number any order has.<BR><BR>I am stumped. It seems to me I need to do a count of OrderDetailIDs GROUPed BY the OrderDetailID, then do a MAX on those counts. I have:<BR><BR>SELECT MAX( countIDS ) AS maxCounts<BR>FROM ( <BR> SELECT COUNT(OrderDetailID) AS countIDs<BR> FROM OrderDetail<BR> GROUP BY OrderDetailID<BR> )<BR><BR>But that errors out, of course. I also tried:<BR><BR>SELECT MAX( SELECT COUNT(OrderDetailID) AS countIDs<BR> FROM OrderDetail<BR> GROUP BY OrderDetailID ) AS maxCounts<BR><BR>I know these are incomplete, but what is the basic logic behind this? How do you do it?<BR><BR>Thanks<BR><BR>Kurt

2. Senior Member
Join Date
Dec 1969
Posts
2,892

## RE: SELECT Max( Count() ) ??

This is a bit closer. The inner query works fine in returning the counts of OrderDetails per OrderID, but still can&#039;t then pull the Max()<BR><BR>SELECT MAX(countIDs) AS maxCount<BR>FROM (<BR> SELECT COUNT(od.OrderDetailID) AS countIDs<BR> FROM OrderDetail AS od<BR> INNER JOIN OrderHeader AS oh<BR> ON od.OrderID = oh.OrderID<BR> GROUP BY oh.OrderID <BR> )

3. Senior Member
Join Date
Dec 1969
Posts
2,437

## RE: SELECT Max( Count() ) ??

But that errors out, of course. <BR><BR>Why do you say of course? What error do you get? <BR><BR>&#060;code&#062;<BR>SELECT MAX( countIDS ) AS maxCounts<BR>FROM (<BR> SELECT COUNT(OrderDetailID) AS countIDs<BR> FROM OrderDetail<BR> GROUP BY OrderDetailID<BR> )dt<BR>&#060;/code&#062;<BR><BR>Derived tables needs a correlation name.

4. Senior Member
Join Date
Dec 1969
Posts
2,892

## RE: SELECT Max( Count() ) ??

Hey Lars:<BR><BR>First off, I should have mentioned this is SQL Server 2k. The original query I showed:<BR><BR>SELECT MAX( countIDS ) AS maxCounts <BR>FROM ( <BR> SELECT COUNT(OrderDetailID) AS countIDs <BR> FROM OrderDetail <BR> GROUP BY OrderDetailID <BR> )<BR><BR>Returns:<BR>Server: Msg 170, Level 15, State 1, Line 6<BR>Line 6: Incorrect syntax near &#039;)&#039;.<BR><BR>I assume something is missing. You said "Derived tables needs a correlation name." An alias? Or is a correlation name different from an alias? Yours did run when I added the dt at the end, but I&#039;m not sure what that did. It also returned the wrong value, so I tried again with:<BR><BR>Actually, tbis wound up working:<BR><BR>SELECT oh.OrderID, COUNT(od.OrderDetailID) AS countIDs <BR> FROM OrderDetail AS od <BR> INNER JOIN OrderHeader AS oh <BR> ON od.OrderID = oh.OrderID <BR> GROUP BY oh.OrderID <BR> ORDER BY COUNT(od.OrderDetailID) DESC<BR>

5. Senior Member
Join Date
Dec 1969
Posts
2,892

## OOps- msg truncated

Meant to also add that I now realize what the dt meant - Derived Table. Ok, makes sense.<BR><BR>Also, thanks for your help.<BR><BR>Kurt

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•