?
Solved

select top 20% from each category

Posted on 2010-09-12
9
Medium Priority
?
857 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
8 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

This is an article on how to answer questions, earn points and become an expert.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

621 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