Rank Function

[Portucale]

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,

[Excel Fox]

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

[Rajivalwar]

Hi

Try this:

SELECT 
	*
FROM

	(

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

	)
WHERE
	Rnk>10