Multiple TOPs

  • 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

  • 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.

  • 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

  • select * from total_spending

    where amount in (select top 6 amount from total_spending order by amount desc)

    raghu


    cynosure

  • 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.

  • 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.

  • 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