If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
querying a percentage of table info
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.? -- Cloudbuster |
#2
|
|||
|
|||
querying a percentage of table info
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". "Cloudbuster" wrote: 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.? -- Cloudbuster |
#3
|
|||
|
|||
querying a percentage of table info
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 sto 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 Cloudbuster wrote: 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.? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200912/1 |
#4
|
|||
|
|||
querying a percentage of table info
"Cloudbuster" .(donotspam) wrote in message ... 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.? -- Cloudbuster 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? |
#5
|
|||
|
|||
querying a percentage of table info
"David F Cox" wrote in message ... "Cloudbuster" .(donotspam) wrote in message ... 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.? -- Cloudbuster 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% |
Thread Tools | |
Display Modes | |
|
|