Reputation:
Database is AdventureWorks.
select COUNT(*) as 'Number of times a product is sold at same quantity'
from
Sales.SalesOrderDetail
group by
OrderQty, ProductID
order by
COUNT(*) desc
This returns data like this:
Number of times a product is sold at same quantity
--------------------------------------------------
4279
3216
3095
2376
2334
2319
2234
2201
2121
2025
1712
1488
1396
1161
1044
and other 2600+ rows.
I am interested in getting 4279 as output.
I cannot apply Max as the it doesn't work with aggregate function or subquery. I tried anyway. Didn't work.
I am guessing I cannot, since count(*) is not a column. But if there is a way:
How can I get max of such output?
Upvotes: 1
Views: 73
Reputation: 263943
just add TOP
to limit the number of results
select TOP 1 COUNT(*) as 'Number of times a product is sold at same quantity'
from Sales.SalesOrderDetail
group by OrderQty, ProductID
order by COUNT(*) desc
UPDATE 1
WITH results
AS
(
select COUNT(*) as [Number of times a product is sold at same quantity],
DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) rank_no
from Sales.SalesOrderDetail
group by OrderQty, ProductID
)
SELECT [Number of times a product is sold at same quantity]
FROM results
WHERE rank_no = 2
Upvotes: 2