SQL Server : select the top 3 items from the top 3 categories

I'm trying to write a query to receive a single data set of the top 3 items from the top 3 categories.

The top categories would be ordered by a sum of the ItemScore column descending grouped by category, for example, Category 1 has a score of 23, Category 2 has a score of 22.

Each item in that category is then ordered by its ItemScore descending.

Here is a list of the raw data.

And here is an example of the expected result.

I hope this makes sense!

UPDATE:

Here is a script I have been working with and some test data;

CREATE TABLE [dbo].[Test]
(
[CategoryID] [int] NULL,
[CategoryName] [varchar](100) NULL,
[ItemID] [int] NULL,
[ItemName] [varchar](100) NULL,
[ItemScore] [int] NULL
)

INSERT Test (CategoryID, CategoryName, ItemID, ItemName, ItemScore)
VALUES (1, 'Category 1', 1, 'Item 1', 1),
(1, 'Category 1', 2, 'Item 2', 2),
(1, 'Category 1', 3, 'Item 3', 7),
(1, 'Category 1', 4, 'Item 4', 1),
(1, 'Category 1', 5, 'Item 5', 1),
(2, 'Category 2', 6, 'Item 6', 1),
(2, 'Category 2', 7, 'Item 7', 1),
(2, 'Category 2', 8, 'Item 8', 9),
(2, 'Category 2', 9, 'Item 9', 10),
(2, 'Category 2', 10, 'Item 10', 2),
(3, 'Category 3', 11, 'Item 11', 1),
(3, 'Category 3', 12, 'Item 12', 1),
(3, 'Category 3', 13, 'Item 13', 2),
(3, 'Category 3', 14, 'Item 14', 1),
(3, 'Category 3', 15, 'Item 15', 2),
(4, 'Category 4', 16, 'Item 16', 5),
(4, 'Category 4', 17, 'Item 17', 6),
(4, 'Category 4', 18, 'Item 18', 3),
(4, 'Category 4', 19, 'Item 19', 5),
(4, 'Category 4', 20, 'Item 20', 1),
(5, 'Category 5', 21, 'Item 21', 2),
(5, 'Category 5', 22, 'Item 22', 8),
(5, 'Category 5', 23, 'Item 23', 5),
(5, 'Category 5', 24, 'Item 24', 3),
(5, 'Category 5', 25, 'Item 25', 4)

SELECT
*,
ROW_NUMBER() OVER (PARTITION BY t.CategoryID ORDER BY t.ItemScore DESC) AS RowNumber
FROM
Test t


2 Answers
2

How about this:

CategoryScore

ItemScore

Putting that in a query, it could look like this:

WITH
summing AS (
SELECT CategoryID, CategoryName, ItemID, ItemName, ItemScore
, SUM(ItemScore) OVER (PARTITION BY CategoryID) AS CategoryScore
FROM Test
),
rating AS (
SELECT CategoryID, CategoryName, ItemID, ItemName, ItemScore
, DENSE_RANK() OVER (ORDER BY CategoryScore DESC, CategoryID) AS GrpNumber
, ROW_NUMBER() OVER (PARTITION BY CategoryID ORDER BY ItemScore DESC) AS RowNumber
FROM summing
)
SELECT CategoryID, CategoryName, ItemID, ItemName, ItemScore, RowNumber
FROM rating
WHERE GrpNumber <= 3
AND RowNumber <= 3
ORDER BY GrpNumber, RowNumber;


You can try to write the query to be a subquery, then get RowNumber 1 to 3

1

3

SELECT * FROM (
SELECT
*,
Row_NUMBER() OVER (PARTITION BY t.CategoryID ORDER BY t.ItemScore DESC) AS RowNumber
FROM Test t
) t1
where RowNumber <=3


sqlfiddle

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.