Solved

SELET TOP 5 IN EACH GROUPING

Posted on 2004-10-08
21
520 Views
Last Modified: 2012-06-27
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
Comment
Question by:STEFANV03
  • 10
  • 4
  • 3
  • +3
21 Comments
 
LVL 16

Expert Comment

by:Nestorio
ID: 12260312
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
 

Author Comment

by:STEFANV03
ID: 12260364
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
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 200 total points
ID: 12260386
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
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:STEFANV03
ID: 12260646
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
 
LVL 77

Expert Comment

by:peter57r
ID: 12260694
Sorry there are two 'in' s - delete one of them.

'WHERE t.sales in  IN '

Pete
0
 

Author Comment

by:STEFANV03
ID: 12260752
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
 
LVL 77

Expert Comment

by:peter57r
ID: 12260901
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
 

Author Comment

by:STEFANV03
ID: 12261002
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
 
LVL 77

Expert Comment

by:peter57r
ID: 12261224
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
 

Author Comment

by:STEFANV03
ID: 12261270
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
 

Author Comment

by:STEFANV03
ID: 12261286
Any other ideas out there?
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12261320
The problem is this:
WHERE indx=T.indx

Try changing it to:
WHERE CATEGORY=T.CATEGORY
0
 
LVL 10

Expert Comment

by:calpurnia
ID: 12261507
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
 

Author Comment

by:STEFANV03
ID: 12261780
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12261922
What is your primary key field? You will need one for this method to work.
0
 

Author Comment

by:STEFANV03
ID: 12261937
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
 
LVL 44

Accepted Solution

by:
GRayL earned 300 total points
ID: 12262422
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
 

Author Comment

by:STEFANV03
ID: 12262594
thanks it works like a charm now!
0
 
LVL 44

Expert Comment

by:GRayL
ID: 12262656
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
 

Author Comment

by:STEFANV03
ID: 12262778
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
 
LVL 44

Expert Comment

by:GRayL
ID: 12262882
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

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

808 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