[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

select top 20% from each category

Posted on 2010-09-12
9
Medium Priority
?
851 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 11

Accepted Solution

by:
asian_niceguy earned 2000 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 93

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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:Tracy
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

650 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