Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 527
  • Last Modified:

SELET TOP 5 IN EACH GROUPING

HI!

I tried to use select top 5 and than group by to get top 5 from each grouping level but it ain't working like this. Anyone has an idea on how you can selet the top 5 records from each group by level in a database?
the database looks kind of like this:
CAT         SALES
1      9
1      10
1      20
1      34
1      2
1      40
1      1
2      5
2      6
2      7
2      8
2      9
2      2
2      3
What I want is to get top 5 sales number from each category. Of course I tried something like:
select top 5 cat sales
from [table]
order by cat , sales
group by cat

but it will only give me top 5 from cat 1 and not look at cat 2.

Any ideas?

Tahnks a million

Valentin
0
STEFANV03
Asked:
STEFANV03
  • 10
  • 4
  • 3
  • +3
2 Solutions
 
NestorioCommented:
Select top 5 from table
Where Cat = 1
Order By Sales Desc
Union
Select top 5 from table
Where Cat = 2
Order By Sales Desc
....
etc.

0
 
STEFANV03Author Commented:
ok that would work, the problem is the table I've written herer is simplified. Cat would be an index that has 50+ different values. an ideas on how I can automate the were cat=  without typing each and every value of the index?
Thanks

Valentin
0
 
peter57rCommented:
Hello STEFANV03,

I think this will do it:

SELECT t.cat, t.sales
FROM mytable as t WHERE t.sales in  IN (SELECT TOP 4 [sales] FROM mytable Where cat = t.cat ORDER BY [sales] DESC) ORDER BY cat, sales DESC

Pete
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
STEFANV03Author Commented:
It looked like it would work but it just crashed my access everytime I tried to run it. the query I'm running this on is only 2500 records...
any other ideas?
0
 
peter57rCommented:
Sorry there are two 'in' s - delete one of them.

'WHERE t.sales in  IN '

Pete
0
 
STEFANV03Author Commented:
I've figured that one out on my own. It crashed after I corrected the syntax..
The query is not really on a totally new table - it's on some other queries - maybe this is what's doing it...
Any other ideas?
V
0
 
peter57rCommented:
I've tested against the sample data you provided and it's fine here.

I assume you have something which is deriving what I have as 'mytable'.
Maybe you can incorporate the sql from that lower query into this query directly?

Or finish the lower level queries with a make-table query which could feed this one.

Pete
0
 
STEFANV03Author Commented:
here is the actual query. MBSS is obviously the table, index is like the cat field in the example and CRT NS$ is sales.
====
SELECT T.INDEX, T.STORE_NAME, T.CATEGORY_NAME, T.PRODUCT_NAME, T.NSU, T.[CRT NS$], T.[gp$], T.[GP%], T.[AVG PRICE], T.[PRV NS$], T.[CRT VS PRV NS$], T.STORE, T.CATEGORY
FROM MBSS AS T
WHERE T.index IN (SELECT TOP 5 INDEX FROM MBSS WHERE INDEX=T.INDEX ORDER BY [CRT NS$] DESC)
ORDER BY STORE, CATEGORY, [CRT NS$];
=====

I am now doing the query on a table but it does not select top 5 from each index, it returns all the values from the table.
Did I screw up something else?
0
 
peter57rCommented:
I can't be sure but I think you might have a problem with a field called Index as it is a SQL reserved word.

Other than that the SQL looks OK to me.

Pete
0
 
STEFANV03Author Commented:
I changed index to indx and ran it again. It still returns all the values in the original table (and not just top 2 by index)...

SELECT T.indx, T.STORE_NAME, T.CATEGORY_NAME, T.PRODUCT_NAME, T.NSU, T.[CRT NS$], T.[gp$], T.[GP%], T.[AVG PRICE], T.[PRV NS$], T.[CRT VS PRV NS$], T.STORE, T.CATEGORY
FROM MBSS AS T
WHERE T.indx IN (SELECT TOP 2 indx FROM MBSS WHERE indx=T.indx ORDER BY [CRT NS$] DESC)
ORDER BY STORE, CATEGORY, [CRT NS$] desc;

0
 
STEFANV03Author Commented:
Any other ideas out there?
0
 
shanesuebsahakarnCommented:
The problem is this:
WHERE indx=T.indx

Try changing it to:
WHERE CATEGORY=T.CATEGORY
0
 
calpurniaCommented:
Can't see why it would be returning *ALL* the values, but if, for instance you had 12 records within a particular CAT with a SALES value of 35, the query would return all 12 records within the result set.
0
 
STEFANV03Author Commented:
index is actually a combination of store and category because i need it for each store and each category. I basically need the top 5 sales [CRT NS$]  for each indx value.
0
 
shanesuebsahakarnCommented:
What is your primary key field? You will need one for this method to work.
0
 
STEFANV03Author Commented:
I do not have a primary key field as this is the result of several other queries. Why would it need a primary key though?
0
 
GRayLCommented:
I got this to work on a table table2 with two fields, cat and sales:

select a.cat, a.sales  from table2 a where a.sales in (select top 5 b.sales from table2 b where b.cat = a.cat);

Apply the logic to your table and I'm sure you will resolve it.  In my test case I had two equal values in the top 5 of cat = 2 and it gave me six records - as it should
0
 
STEFANV03Author Commented:
thanks it works like a charm now!
0
 
GRayLCommented:
Thanks, glad I could help.  What did we have to do to get an A?  If "it works like a charm now!" only earns a B, I wonder what the A solution would have done ;)
0
 
STEFANV03Author Commented:
it felt like a b - I guess because the solution wasn't in my sql statement and I had to adapt it.
I did not think much about it - kinda' makes me feel guilty now...
0
 
GRayLCommented:
I understand.  Its just that your SQL statement, which didn't work,  contained a lot of stuff that was there because of the construct.  I thought it best to get the sample to work and with that logic base, create the SQL for your specific table.  I thought about giving you back your SQL but I thought you could adapt the logic to the table full of funny names and Access reserved words better than I could.  ;))
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 10
  • 4
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now