slothnet
asked on
select top 20% from each category
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.
.
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.
.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
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.
Seems like that should do it.
ASKER
Worked out fine.
ASKER
@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
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
ASKER
@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
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
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.
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