I have a products table with prices
| ProductID | UnitPrice |
|---|---|
| 1 | 1.00 |
| 2 | 3.00 |
| 4 | 3.00 |
| 5 | 6.00 |
| 6 | 2.00 |
| 7 | 4.00 |
I am trying to find product id for max price
| ProductID | UnitPrice |
|---|---|
| 5 | 6.00 |
I have tried
select ProductID
from Products
where UnitPrice = max(UnitPrice)
But I get an error:
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
New contributor
Dogukan Çağrı Destegüloğlu is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
2
Find all products that share the maximum UnitPrice:
SELECT * FROM Products WHERE UnitPrice = (SELECT max(UnitPrice)
FROM Products)
If SQL-Server 2008+
--Top 1 product that matches the max UnitPrice
WITH allProducts AS
(
SELECT ProductId,UnitPrice,
ROW_NUMBER() OVER (ORDER BY UnitPrice DESC) ROW_NUM
FROM Products
)
SELECT ProductId,UnitPrice
FROM allProducts
WHERE ROW_NUM = 1
--All products that match the max unit price
WITH allProducts AS
(
SELECT ProductId,UnitPrice,
Max(UnitPrice) OVER() as MaxUnitPrice
FROM Products
)
SELECT ProductId,UnitPrice
FROM allProducts
WHERE UnitPrice=MaxUnitPrice
fiddle