Solved

select top 20% from each category

Posted on 2010-09-12
9
829 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 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Outlook Free & Paid Tools
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

726 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