Solved

select top 20% from each category

Posted on 2010-09-12
9
796 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:slothnet
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:slothnet
Comment Utility
Worked out fine.
0
 

Author Comment

by:slothnet
Comment Utility
@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
Comment Utility
@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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now