Search code examples
sqlsql-server-2008greatest-n-per-group

SQL: cte bring back top result each category

Using SQl server 2008.

;WITH cte AS
(
   SELECT *,
     ROW_NUMBER() OVER (PARTITION BY refId ORDER BY refid DESC) AS rn
   FROM Products
)
SELECT ProductCategories.Id, 
ProductCategories.Title, 
ProductCategories.languageId, 
ProductCategories.RefId, 
Url
FROM 
 cte
RIGHT JOIN
 RoundupAcademy.dbo.ProductCategories
ON 
 dbo.ProductCategories.RefID = cte.CategoryId
WHERE rn = 1

This query should bring back top result of each category instead it just brings back all results.

i just need the top one based on refid, what am i doing wrong here?

Solution

  • For those that wish to know, the query is fine except the reference for the partition was wrong, it should of been categoryId not refID.