querying a percentage of table info

[Cloudbuster]

If there are 3 unique groups listed in a table, is there a way I can make a
query that will show me the first 20% of records from each group, 2nd 20%,
etc.?

 

[Dorian]

From Access HELP:

ALL, DISTINCT, DISTINCTROW, TOP Predicates
Specifies records selected with SQL queries.

Syntax
SELECT [ALL | DISTINCT | DISTINCTROW | [TOP n [PERCENT]]]
FROM table


-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[KenSheridan via AccessMonster.com]

You can put a subquery in the query's WHERE clause to identify the top 20% of
rows per group and use it to restrict the rows returned by the query, e.g. if
you want to return the top 20% of sales by currency amount per store:

SELECT *
FROM StoreSales AS S1
WHERE Amount IN
(SELECT TOP 20 PERCENT Amount
FROM StoreSales As S2
WHERE S2.Store = S1.Store
ORDER BY Amount DESC)
ORDER BY Store, Amount DESC;

The subquery is correlated with the outer query on the Store column, so it
returns the top 20% of rows by the amount in descending order for the outer
query's current store. By restricting the rows returned by the outer query
to those where the amount is in the set of values returned by the subquery,
only those rows per store where the amount is in the top 20% for that store
are returned. Note how the two instances of the StoreSales table are
differentiated by giving them aliases S1 and S2. The outer query can be
ordered any way you wish, but the subquery must be ordered on the column by
which the top 20% is deteremined.

Ken Sheridan
Stafford, England

 

[David F Cox]

If there are 3 unique groups listed in a table, is there a way I can make
a
query that will show me the first 20% of records from each group, 2nd 20%,
etc.?

maybe I don't understand the question or don't understand the answers.

I would get a count of the records in each group. I would generate a
sequence number for each item in each group, and do where between queries
using those sequence numbers to split into five groups.

and then, because I am cusious, I would then compare the speed of that
method with taking the top 20 percent as one query, run the second excluding
those in the first query.

Then the third excluding the results in the first two queries.

and so on to get the fourth and fifth groups.

all speculation, not tried.

Shouldn't we all be out partying?

 

[David F Cox]

maybe I don't understand the question or don't understand the answers.

I would get a count of the records in each group. I would generate a
sequence number for each item in each group, and do where between queries
using those sequence numbers to split into five groups.

and then, because I am cusious, I would then compare the speed of that
method with taking the top 20 percent as one query, run the second
excluding those in the first query.

Then the third excluding the results in the first two queries.

and so on to get the fourth and fifth groups.

all speculation, not tried.

Shouldn't we all be out partying?

I should have added that if excluding the top 20% on the second query it
will be necessary to select top 25% because 1/5 are missing.

On the third query it will be top 33 1/3 % , fourth top 50%