June 27, 2009 at 8:27 am
Hi,
I have a tough problem, which at the surface seems easy, but has proven to be very difficult. I have a table with the following attributes:
Date
Quantity
Task
I basically need to obtain the "Top 3" Tasks for each Day (but only the Tasks which have the highest Quantity count).
Example:
Dte Qty Task
6/1/2009 1 church
6/1/2009 1 store
6/1/2009 1 mall
6/1/2009 1 dogwalking
6/1/2009 1 mowing
6/1/2009 1 cleaning
6/1/2009 1 church
6/1/2009 1 church
6/1/2009 1 cleaning
6/2/2009 1 church
6/2/2009 1 store
6/2/2009 1 fixing
6/2/2009 1 dogwalking
6/2/2009 1 mowing
6/2/2009 1 washing
6/2/2009 1 church
6/2/2009 1 laundry
6/2/2009 1 cleaning
I've been struggling. Here''s a start:
select Top 3 Dte,sum(Qty) as Qty,Task
from Table
group by Dte,Task
order by Qty desc
But as you can see this will only return 3 total rows. I need 3 total rows for each day. How would I rewrite the query to produce 3 rows for each Date?
So then I tried some inner joins and even using cross apply and many other variations with no luck. There must be a quick way to so this 😛
Thank you for any help !
Paul
June 27, 2009 at 10:23 am
I think this should get you started. I did nothing to eliminate ties. Since this is a 2005 forum I provided a 2005 answer.
DECLARE @tasks TABLE (dte SmallDATETIME, quantity SmallINT, task VARCHAR(25))
INSERT INTO
@tasks (dte, quantity, task)
SELECT
'6/1/2009',
1,
'church'
UNION ALL
SELECT
'6/1/2009',
1,
'store'
UNION ALL
SELECT
'6/1/2009',
1,
'mall'
UNION ALL
SELECT
'6/1/2009',
1,
'dogwalking'
UNION ALL
SELECT
'6/1/2009',
1,
'mowing'
UNION ALL
SELECT
'6/1/2009',
1,
'cleaning'
UNION ALL
SELECT
'6/1/2009',
1,
'church'
UNION ALL
SELECT
'6/1/2009',
1,
'church'
UNION ALL
SELECT
'6/1/2009',
1,
'cleaning'
UNION ALL
SELECT
'6/2/2009',
1,
'church'
UNION ALL
SELECT
'6/2/2009',
1,
'store'
UNION ALL
SELECT
'6/2/2009',
1,
'fixing'
UNION ALL
SELECT
'6/2/2009',
1,
'dogwalking'
UNION ALL
SELECT
'6/2/2009',
1,
'mowing'
UNION ALL
SELECT
'6/2/2009',
1,
'washing'
UNION ALL
SELECT
'6/2/2009',
1,
'church'
UNION ALL
SELECT
'6/2/2009',
1,
'laundry'
UNION ALL
SELECT
'6/2/2009',
1,
'cleaning'
;WITH cteRank AS
(
SELECT
ROW_NUMBER() OVER(PARTITION BY dte ORDER BY SUM(quantity) desc) AS row,
RANK() OVER(PARTITION BY dte ORDER BY SUM(quantity) desc) AS rnk,
DENSE_RANK() OVER(PARTITION BY dte ORDER BY SUM(quantity) desc) AS dense_rnk,
dte,
SUM(quantity) AS qty,
task
FROM
@tasks
GROUP BY
dte,
task
)
SELECT
dense_rnk,
row,
rnk,
dte,
task,
qty
FROM
cteRank
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 27, 2009 at 11:15 am
Your solution works perfectly. Thank you so much !!!:-)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply