Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jun 2001
    Posts
    15
    Thanks
    1
    Thanked 0 Times in 0 Posts

    How to get Top 25 rows by each group

    I have a table in Access 2007 with almost 200,000 rows with multiple columns for which I need to find the top 25 rows for each group.

    The important columns are STOREID (store ID), CUSTID (Customer ID), and QTY (Order amount).

    I want to select 25 rows per store that reflect the top 25 customers in descending order by their order amount values.

    SELECT TOP 25... only gives me the top 25 rows regardless of store so I'm missing something.

    Can anyone help with this?

    Thanks in advance!

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Assuming your table is called tblTransactions, and each record has a unique identifier TransactionID, I think this SQL will do it. This is a query with a subquery.

    Code:
    SELECT tblTransactions.*
    FROM tblTransactions
    WHERE ((([TransactionID]) In (SELECT TOP 25 T.TransactionID
        FROM tblTransactions AS T
        WHERE (((T.StoreID)=tblTransactions.StoreID))
        ORDER BY T.qty DESC)))
    ORDER BY tblTransactions.StoreID,  tblTransactions.qty DESC;
    Regards
    John



  3. #3
    New Lounger
    Join Date
    Jun 2001
    Posts
    15
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Unfortunately, there is no unique identifier on the table. From my research, what I really need is DENSE_RANK() but I understand that it doesn't exist in Access 2007.

    For example (although I'm not sure that the syntax is correct):

    Code:
    SELECT STOREID
        , CUSTID
        , QTY
        , DENSE_RANK() OVER (ORDER BY QTY) AS RowRank
    FROM tblTransactions
    WHERE RowRank <= 25
    ORDER BY STOREID, CUSTID, QTY DESC
    
    Is there something like this in Access 2007?

    Thanks for the help!

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I have not heard of dense rank before.

    What about this?

    The Dcount counts the number of records where the StoreID is the same and the Qty is more than the current one, then we restrict the results to those where this rank is less than (or =) 25.

    Code:
    SELECT tblTransactions.*, 1+DCount("*","tbltransactions","([StoreID]=" & [StoreID] & ") and ([qty]>" & [qty] & ")") AS Rank
    FROM tblTransactions
    WHERE (((1+DCount("*","tbltransactions","([StoreID]=" & [StoreID] & ") and ([qty]>" & [qty] & ")"))<=25))
    ORDER BY tblTransactions.StoreID, tblTransactions.Qty Desc;
    Regards
    John



  5. The Following User Says Thank You to johnhutchison For This Useful Post:

    zreclay (2011-04-11)

  6. #5
    New Lounger
    Join Date
    Jun 2001
    Posts
    15
    Thanks
    1
    Thanked 0 Times in 0 Posts
    That mostly works but it isn't exactly what we want. For the 1st thru 24th QTY values, only the first row having that rank is shown. If there are multiple rows for the 25th rank, then all rows having that rank are shown.

    We need to see the top 25 rows per store, even if there are only a few distinct QTY values within that store "group" because there are probably mulitple CUSTIDs that have the same QTY values. We just want to see the first 25 rows for each store.

    We've decided to write a macro that will pull the ranked rows by individual store and append them to another table. We can do this with SELECT TOP 25...

    Thanks for your help, though.

  7. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    TRy this article and see if it helps:
    http://www.pcreview.co.uk/forums/con...-t3856858.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •