By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,473 Members | 1,278 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,473 IT Pros & Developers. It's quick & easy.

Multiple Top Results

P: 73
I have a query that counts how many results are in each branch out of 5000 rows. I am trying to condense even further and only show the Top 2 Branches in every State (so should reflect 100 results, theoretically). Is there a way to get this done?
Expand|Select|Wrap|Line Numbers
  1. SELECT Format(a.[Month End DT],"mm/""01""/yy") AS Date_ID, a.STATE, a.Branch, Count(a.Branch) AS Branch_Totals
  2. FROM [January Population] AS a
  3. GROUP BY a.STATE, a.Branch, a.[MONTH END DT]
Feb 21 '08 #1
Share this Question
Share on Google+
8 Replies


Expert 100+
P: 374
Multiple Top Results
--------------------------------------------------------------------------------

I have a query that counts how many results are in each branch out of 5000 rows. I am trying to condense even further and only show the Top 2 Branches in every State (so should reflect 100 results, theoretically). Is there a way to get this done?

SELECT Format(a.[Month End DT],"mm/""01""/yy") AS Date_ID, a.STATE, a.Branch, Count(a.Branch) AS Branch_Totals
FROM [January Population] AS a
GROUP BY a.STATE, a.Branch, a.[MONTH END DT]
The simplest way is simply insert in the SELECT Statement, TOP 2 after the word SELECT and before your FORMAT statement.

this way it will tell the query you only want 2 rows of data.

Hope that helps,

Joe P.
Feb 21 '08 #2

P: 73
The simplest way is simply insert in the SELECT Statement, TOP 2 after the word SELECT and before your FORMAT statement.

this way it will tell the query you only want 2 rows of data.

Hope that helps,

Joe P.

Well thats the problem I don't want to show just the Top 2 records (I already tried that). I want it to reflect the Top 2 Branches in every State so it will have about 100 results rougly (50 States with top 2 branches = 100 results). I ran a Max Function and it gave the 1 result for each state but I need to run this with the top 2 results. Am I making sense? Can you help with doing this?
Feb 21 '08 #3

Scott Price
Expert 100+
P: 1,384
You'll need to use the Top 2 to find the branches, then include this in a second query that pulls the results associated with each branch.

Regards,
Scott

p.s. Welcome on board, PianoMan! Glad to have you as part of our Expert team.
Feb 21 '08 #4

P: 73
You'll need to use the Top 2 to find the branches, then include this in a second query that pulls the results associated with each branch.

Regards,
Scott

p.s. Welcome on board, PianoMan! Glad to have you as part of our Expert team.

Hello Scott,

I am confused on how to do this.....The Top 2 function when is run only gives me 2 results. Are u saying that I have to make 50 queries and run the top 2 for each state that way? Can you help me with the SQL (written above) to see what you are referring to.

Thanks very much,

Matt
Feb 21 '08 #5

Scott Price
Expert 100+
P: 1,384
I was not thinking too deeply at the point of making that comment, sorry :-) However, I still think that you will need to do it this way.

The only way I can think of to accomplish it is to write this into VBA code, using a loop to find the Top 2 for each state. These can be set into variables which then are used to gather the other results you require.

I don't have my SQL book with me, so I can't research this assertion like I'd like, but I doubt you'll be able to do this with one or even two SQL queries.

Regards,
Scott
Feb 22 '08 #6

NeoPa
Expert Mod 15k+
P: 31,768
Please visit this thread to see why we would rather you didn't double-post (Please do Not Double Post).

Apart from showing quite bad manners to anyone that has already spent time trying to help with your question, it also causes the moderators extra head-aches to tidy up the mess you create.

I'll close the other thread (SQL-Top Numbers) as you seem to have ignored it anyway.

Admin.
Feb 22 '08 #7

P: 73
Please visit this thread to see why we would rather you didn't double-post (Please do Not Double Post).

Apart from showing quite bad manners to anyone that has already spent time trying to help with your question, it also causes the moderators extra head-aches to tidy up the mess you create.

I'll close the other thread (SQL-Top Numbers) as you seem to have ignored it anyway.

Admin.

Sorry, I checked the box and then went to Delete Subscription for the SQL Top Numbers. Is that not the way to delete it?
Feb 22 '08 #8

NeoPa
Expert Mod 15k+
P: 31,768
OK - Understood.

In answer though - no it's not. You don't have rights to delete threads - even if you are the OP of the thread. Deleting from your subscription list simply stops the site from keeping you updated as to any activity therein. Typically this is not something you should do except perhaps in very rare cases.

Remember, as soon as it's public there are people possibly devoting time and energy to the problem. It isn't a good idea simply to stop responding to it. Although, as I said, I understand your misconception so no big deal.
Feb 22 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.