Top values

[Esperanza]

Hello everyone,
I want to return the top 5 values by group in a query, example,
if I use the following code, I got only the 5 first records in the result.
Let say for each product, I have 12 different prices, I want in the result
only 5 prices per product.

Thanks a lot !!
Esperanza

SELECT Top 5 ProductName, ProductUnitPrice
FROM Products
GROUP BY ProductName, ProductUnitPrice
ORDER BY ProductUnitPrice Desc;

 

[John Spencer (MVP)]

You need a subquery. That would probably look something like:

SELECT ProductName, ProductUnitPrice
FROM Products
WHERE ProductUnitPrice IN (
SELECT Top 5 T.ProductUnitPrice
FROM Products as T
WHERE T.ProductName = Products.ProductName
ORDER BY T.ProductUnitPrice Desc)

 

[Esperanza]

Thanks, it works greats !

Esperanza

You need a subquery. That would probably look something like:

SELECT ProductName, ProductUnitPrice
FROM Products
WHERE ProductUnitPrice IN (
SELECT Top 5 T.ProductUnitPrice
FROM Products as T
WHERE T.ProductName = Products.ProductName
ORDER BY T.ProductUnitPrice Desc)