September 3, 2003 at 12:36 pm
Hi,
I got a SQL question: Say I got a table total_spending (store,dept,amount) with a list of stores, their departments, and how much money was spent in them. I want to see, for each store, the top 6 senders. I know I can do that with a cursor, But I want to know if there is a way to do it in 1 SQL (with subqueries, I assume). Any idea, anyone?
Thanks
Yoni – yonision@hotmail.com
September 4, 2003 at 12:22 pm
Although the topic that Steve pointed you to is very nice I think you could do this with a single subquery. If I understand what you want correctly you might be able to do this as follows...
SELECT
ts.Store,
tp.Department,
tp.Amount
FROM total_spending ts
JOIN (SELECT TOP 6 Store, Department, Amount FROM total_spending ORDER BY Amount Desc) tp
ON ts.Store = tp.Store AND ts.Department = tp.Department
Please note that I haven't tested this so it probably needs work 🙂
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
September 4, 2003 at 1:38 pm
THis would work, I think this one might be faster but not 100%
SELECT ts.Store, tp.Department, tp.Amount
FROM total_spending ts where ts.store in (SELECT TOP 6 Store FROM total_spending ORDER BY Amount Desc)
Ray Higdon MCSE, MCDBA, CCNA
------------
Ray Higdon MCSE, MCDBA, CCNA
September 4, 2003 at 2:41 pm
select * from total_spending
where amount in (select top 6 amount from total_spending order by amount desc)
raghu
cynosure
September 10, 2003 at 9:06 am
Hmm... I'm afraid these queries won't accomplish the goal (at least as I understand it), which is to select top 6 departments from EACH store - not the top 6 from all. I tried to think of some solution, but found nothing that would be better than what's already mentioned in the thread to which Steve Jones pointed in the first reply.
September 11, 2003 at 8:22 am
In another thread somewhere I posted a solution for this type of problem that used a temp table instead of cursor. I basically
Created a temp table (Store,Department,Amount,idno) order by
Store ASC ,Department ASC ,Amount DESC
Update table setting idno incremented by 1 (starting from 1 for each Store/Department)
Select from temp table where idno <= 6
Far away is close at hand in the images of elsewhere.
Anon.
September 11, 2003 at 4:23 pm
You might try this - it's not real efficient, but is fairly straightforward.
SELECT T2.StoreID, T2.DeptID, T2.AmtSpent
from total_spending T2 (NOLOCK)
where ( SELECT COUNT(*) From total_spending T1 (NOLOCK)
WHERE T1.StoreID = T2.StoreID
AND T1.AmtSpent >= T2.AmtSpent ) <= 3
ORDER BY T2.StoreID, T2.AmtSpent DESC, T2.DeptID
Guarddata-
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply