October 2, 2018 at 12:24 pm
Hello, I am looking to create a query where I can return the top 3 results for 3 specific categories. See my example code below ... and many thanks in advance!
CREATE TABLE #t (ID int identity (1,1), EName varchar(100), UnitsSold int, Category varchar(10))
INSERT INTO #t (EName, UnitsSold, Category) VALUES ('Nik', 12, 'Red')
INSERT INTO #t (EName, UnitsSold, Category) VALUES ('Bob', 10, 'Red')
INSERT INTO #t (EName, UnitsSold, Category) VALUES ('Luis', 10, 'Red')
INSERT INTO #t (EName, UnitsSold, Category) VALUES ('James', 8, 'Red')
INSERT INTO #t (EName, UnitsSold, Category) VALUES ('Brendan', 5, 'Red')
INSERT INTO #t (EName, UnitsSold, Category) VALUES ('Frank', 22, 'Green')
INSERT INTO #t (EName, UnitsSold, Category) VALUES ('Donna', 17, 'Green')
INSERT INTO #t (EName, UnitsSold, Category) VALUES ('Karen', 12, 'Green')
INSERT INTO #t (EName, UnitsSold, Category) VALUES ('Ray', 11, 'Green')
INSERT INTO #t (EName, UnitsSold, Category) VALUES ('Jenny', 2, 'Green')
INSERT INTO #t (EName, UnitsSold, Category) VALUES ('David', 18, 'Blue')
INSERT INTO #t (EName, UnitsSold, Category) VALUES ('Ana', 11, 'Blue')
INSERT INTO #t (EName, UnitsSold, Category) VALUES ('Mike', 3, 'Blue')
INSERT INTO #t (EName, UnitsSold, Category) VALUES ('Heather', 2, 'Blue')
INSERT INTO #t (EName, UnitsSold, Category) VALUES ('Scott', 0, 'Blue')
INSERT INTO #t (EName, UnitsSold, Category) VALUES ('Lori', 18, 'Violet')
INSERT INTO #t (EName, UnitsSold, Category) VALUES ('Adam', 11, 'Violet')
INSERT INTO #t (EName, UnitsSold, Category) VALUES ('Megan', 3, 'Violet')
INSERT INTO #t (EName, UnitsSold, Category) VALUES ('Andy', 2, 'Violet')
INSERT INTO #t (EName, UnitsSold, Category) VALUES ('Buddy', 0, 'Violet')
-- Query: For Category colors Red, Green, and Blue only, show the top 3 UnitsSold per category
-- Desired Results -----
-- Nik 12 Red
-- Bob 10 Red
-- Luis 10 Red
-- Frank 22 Green
-- Donna 17 Green
-- Karen 12 Green
-- David 18 Blue
-- Ana 11 Blue
-- Mike 3 Blue
October 2, 2018 at 12:50 pm
SELECT EName, UnitsSold, Category
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY Category ORDER BY UnitsSold DESC) AS row_num
FROM #t
WHERE Category IN ('Blue', 'Green', 'Red')
) AS query1
WHERE row_num <= 3
ORDER BY Category, UnitsSold DESC
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 2, 2018 at 12:52 pm
Try Rank
select * from (
select EName, UnitsSold, Category,
Rank() over (partition by category order by UnitsSold desc) Ranking
from #t) t
where Ranking <= 3
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 2, 2018 at 2:12 pm
What have you tried? HINT: ROW_NUMBER() is probably the simplest approach.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 2, 2018 at 4:43 pm
WITH t (EName, UnitsSold, Category)
AS
(
SELECT *
FROM (VALUES ('Nik', 12, 'Red')
, ('Bob', 10, 'Red')
, ('Luis', 10, 'Red')
, ('James', 8, 'Red')
, ('Brendan', 5, 'Red')
, ('Frank', 22, 'Green')
, ('Donna', 17, 'Green')
, ('Karen', 12, 'Green')
, ('Ray', 11, 'Green')
, ('Jenny', 2, 'Green')
, ('David', 18, 'Blue')
, ('Ana', 11, 'Blue')
, ('Mike', 3, 'Blue')
, ('Heather', 2, 'Blue')
, ('Scott', 0, 'Blue')
, ('Lori', 18, 'Violet')
, ('Adam', 11, 'Violet')
, ('Megan', 3, 'Violet')
, ('Andy', 2, 'Violet')
, ('Buddy', 0, 'Violet')) t(a, b, c))
, i AS (SELECT t.*, Row_Number() OVER (PARTITION BY t.Category ORDER BY UnitsSold DESC) ID FROM t)
SELECT i.EName
, i.UnitsSold
, i.Category
FROM i
WHERE i.ID<4
AND i.Category IN ('Red', 'Green', 'Blue');
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply