Multiple Top Results

[Supermansteel]

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?
[CODE=sql]
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][/CODE]

[PianoMan64]

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.

[Supermansteel]

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?

[Scott Price]

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.

[Supermansteel]

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

[Scott Price]

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

[NeoPa]

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.

[Supermansteel]

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?

[NeoPa]

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.