Solved

select top 20% from each category

Posted on 2010-09-12
9
816 Views
Last Modified: 2013-11-27
I took a shot at this statement,  I get a runtime 3138 error,  "syntax error in order by clause."

SELECT TOP 20 percent * into test FROM Products ORDER BY UnitPrice DESC , group by category

I assume this statement without the group by would get the first 20 highest priced products (if there were 100).    My problem is similar in that I have multiple categories, and would like the highest priced 20 percent , for each category, putting a group by at the end was what preliminary logic told me to atleast try.  





.  
0
Comment
Question by:slothnet
9 Comments
 
LVL 11

Accepted Solution

by:
asian_niceguy earned 500 total points
ID: 33659930
Have a look at the link below, he has tackled this very similar problem. You just need to change the select statements with your own select TOP statements

http://www.bennadel.com/blog/1114-Selecting-Top-X-From-Each-Group.htm

Also view the comments section for other users solutions.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 33660302
Although the logic in the link might be ok , the sql is for sql server not Access.

This will give a Select query to select the records. when you are happy that this is working OK just convert it into a make table query using the menu option.

Select * from products as p where p.productid in
(SELECT TOP 25 PERCENT Products.productID
FROM Products where category = p.category
ORDER BY Products.[UnitPrice] DESC)
order by p.category, [unitprice] desc

0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33661962
slothnet,Couple of syntax problems:1) ORDER BY should be the last clause2) Your GROUP BY uses only one column.  Your SELECT clause used *, signifying all columns.  That is a problem if you have >1 column in the table (and we know you do, because you refer to a UnitPrice column in ORDER BY): when you use GROUP BY, then you need to include all of the non-aggregate columns from the SELECT clauseAs the other Experts have noted, you may also have some logic issues, depending on just what it is you're trying to do.Patrick
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:slothnet
ID: 33667167
That link hints me to make a sub that createst a recordset of the distinct categories, then loops throught the values of each distinc category and calls another sub, with the category sent in as a criteria, to a statement like this    Insert Into tbltest Select (sql in op - group by)  where (criteria).

Seems like that should do it.


0
 

Author Comment

by:slothnet
ID: 33669405
Worked out fine.
0
 

Author Comment

by:slothnet
ID: 33669453
@peterr57r
Just noticed your code, didn't try it as I did it another way and am done.

I had to create an empty table and had to create aliases for this to work, since it didn't otherwise.  I loop through a recordset of distinct categories, adds them into test table as it goes.

insert into test select TOP 10 percent [products] as prod, [unit price] as up, category as cat FROM tblproducts Where filename = 'rs' ORDER BY [price] DESC "


So I can achieve the same putting insert into,  in front of your code? Without a recordset
0
 

Author Comment

by:slothnet
ID: 33669533
@peter57r

Didn't work,  took too long so I though it was the amount of records of the table,,, then I tried it on a smaller table and did a quick append but didn't achieve desired results.   I was willing to believe it was going to work but what the hell do I know.  haha
0
 
LVL 24

Expert Comment

by:broomee9
ID: 34995515
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Archiving Access table older than 6 months 9 39
IIF help, YN field 7 22
Binding recordsets to a form 6 27
Access 2007 - Create Query That Returns One Record Per Customer 4 31
This article will show you how to use shortcut menus in the Access run-time environment.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question