Results 1 to 3 of 3

Thread: Rank Function

  1. #1
    Junior Member
    Join Date
    Oct 2012
    Posts
    11
    Rep Power
    0

    Rank Function

    Hi,

    I need to produce a query where I should only extract the top 10 records according to the rank within the table and for a determined period.

    example:
    Table contains 500 records for 2 months worth of data (lets say Dec 12 and Jan 13)
    Query to show Location and Sales
    Query results to give ONLY the TOP10 according to the sales and for the respective month.

    Any help is very much appreciated.

    Thanks,

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Can you give the exact design of your table? And also, the top 10 should be based on the top 10 records, or all records that fall in the top 10 rank, even if multiple entities have the exact same sales
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    May 2011
    Posts
    3
    Rep Power
    0
    Hi

    Try this:


    Code:
    SELECT 
    	*
    FROM
    
    	(
    
    		SELECT 
    			*, 
    			DCOUNT("OrderID","SalesTable","(Month = '" & Month &"' AND Sales > " & Sales &")") As Rnk 
    		FROM 
    			SalesTable
    		ORDER BY
    			Month,
    			Sales DESC
    
    	)
    WHERE
    	Rnk>10

Similar Threads

  1. UDF (user defined function) replacement for Excel's DATEDIF function
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 21
    Last Post: 03-07-2015, 09:47 PM
  2. Replies: 2
    Last Post: 03-17-2013, 06:07 PM

Posting Permissions

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