Solved

SELET TOP 5 IN EACH GROUPING

Posted on 2004-10-08
21
516 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now