Counting in a query - need help!

  • Thread starter Thread starter JD
  • Start date Start date
J

JD

Hi there.. I need to run a query that counts the number of times any
customer ID shows up in the table.. but no more than 10 instances of
each to be in the output. So, if a customer ID is in the table 3
times, then all 3 rows output, if the customer ID is in the table 10
times, then all 10 rows output and if the customer ID is in the table
20, then only the first 10 rows output. (Sorry if this is confusing..
it's hard to type out what I'm looking for!). Is there a way to do
this? Preferably a query.. but I'll take any ideas! Thanks!
 
You might try a query whose SQL looks something like this:

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
[Your Table].[Your Primary Key Field] IN
(SELECT TOP 10
[Your Table].[Your Primary Key Field]
FROM
[Your Table] AS [Self]
WHERE
[Self].[Customer ID] = [Your Table].[Customer ID]
ORDER BY
[Self].[Your Ordering Field])

This assumes:

1. Your table is named "Your Table".

2. Your table has a primary key composed of a single field named "Your
Primary Key Field".

3. Your table has a field named "Your Ordering Field" that you want to use
to determine which are the "first" 10 rows for a customer. For example, if
your table represents customer orders, this field might contain the order
date.

If the value of "Your Ordering Field" is not unique for a given customer
(for example, if a customer may place more than one order on the same date),
you may get more than 10 records for some customers. If you want to prevent
this, add whatever fields to the ORDER BY clause you need to "break the
ties" -- that is, to uniquely rank the rows.

Hope this helps.
 
Back
Top