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

top x of group

P: 2
I want to create a query that is limited to the top x (according to one field) of records that have the same data in another field.

For example, let's say I want to list the top 5 cities by population in each state. I have a database that covers thousands of U.S. cities and has these fields: State_Name, City_Name, Population. I make a query with these 3 fields and sort it in ascending order by State_Name and in descending order by Population. How do I make it show _only_ the 5 highest-population cities of each state?

This is probably very simple, but I thank anyone who can suggest an answer.
Mar 5 '07 #1
Share this Question
Share on Google+
15 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
I want to create a query that is limited to the top x (according to one field) of records that have the same data in another field.

For example, let's say I want to list the top 5 cities by population in each state. I have a database that covers thousands of U.S. cities and has these fields: State_Name, City_Name, Population. I make a query with these 3 fields and sort it in ascending order by State_Name and in descending order by Population. How do I make it show _only_ the 5 highest-population cities of each state?

This is probably very simple, but I thank anyone who can suggest an answer.
Don't know if this will work but it's worth a try.

Expand|Select|Wrap|Line Numbers
  1. SELECT State_Name,
  2. (SELECT Top 5 City_Name 
  3. FROM TableName As T1 INNER JOIN T2 
  4. ON T1.State_Name=T2.StateName
  5. ORDER BY Population DESC)
  6. Population
  7. FROM TableName As T2;
  8.  
Mary
Mar 6 '07 #2

NeoPa
Expert Mod 15k+
P: 31,492
I want to create a query that is limited to the top x (according to one field) of records that have the same data in another field.

For example, let's say I want to list the top 5 cities by population in each state. I have a database that covers thousands of U.S. cities and has these fields: State_Name, City_Name, Population. I make a query with these 3 fields and sort it in ascending order by State_Name and in descending order by Population. How do I make it show _only_ the 5 highest-population cities of each state?

This is probably very simple, but I thank anyone who can suggest an answer.
It's not so much simple as theoretically impossible.
Maybe I overstate the case, as I often find that people can come up with 'out-of-the-box' solutions that seem to break the established rules, but I can see no possible way of implementing this I'm afraid.
Mar 8 '07 #3

ADezii
Expert 5K+
P: 8,638
It's not so much simple as theoretically impossible.
Maybe I overstate the case, as I often find that people can come up with 'out-of-the-box' solutions that seem to break the established rules, but I can see no possible way of implementing this I'm afraid.
I think I may be able to attain a solution via VBA code with the end results (5 highest cities population wise by State) being written to a Table. Do you think it is worth the effort, or just a waste of time? I'm truly interested in your opinion because I've played with it myself and could not arrive at a solution via the SQL Route.
Mar 8 '07 #4

NeoPa
Expert Mod 15k+
P: 31,492
I expect you can ADezii (knowing how familiar you are with the coding side of things).
I should have been clearer in my earlier comment though :
"This is (I think) impossible to do in a query (SQL) alone."
Remember though, any TOP 5 query executed within this code must be executed for each TOP 5 set required. It will not work if generic SQL is used and simply processed through again and again.
Mar 8 '07 #5

ADezii
Expert 5K+
P: 8,638
I expect you can ADezii (knowing how familiar you are with the coding side of things).
I should have been clearer in my earlier comment though :
"This is (I think) impossible to do in a query (SQL) alone."
Remember though, any TOP 5 query executed within this code must be executed for each TOP 5 set required. It will not work if generic SQL is used and simply processed through again and again.
Thanks NeoPa:
Don't think it will be worth the effort to generate the code simply to see if it 'can be done'. It's not like anyone will actually use it.
Mar 8 '07 #6

NeoPa
Expert Mod 15k+
P: 31,492
The OP's gone AWOL you mean. Guess it wasn't such a pressing need ;)
You can always do it if they come back and respond with a request any time. I wouldn't waste your time otherwise though.
Mar 9 '07 #7

ADezii
Expert 5K+
P: 8,638
The OP's gone AWOL you mean. Guess it wasn't such a pressing need ;)
You can always do it if they come back and respond with a request any time. I wouldn't waste your time otherwise though.
Advice well taken
Mar 9 '07 #8

Rabbit
Expert Mod 10K+
P: 12,366
Expand|Select|Wrap|Line Numbers
  1. SELECT Temp.State_Name, Temp.City_Name, Temp.Population
  2. FROM Table1 AS Temp
  3. WHERE City_Name In(SELECT TOP 5 City_Name FROM Table1 WHERE State_Name = Temp.State_Name ORDER BY Population DESC;);
Just replace Table1 with the name of your table.
Mar 9 '07 #9

NeoPa
Expert Mod 15k+
P: 31,492
Lucky I added the (I think) bit in there.
Nice one Rabbit. I thought of this thread when I saw your post in the other thread (Adding a Rank column to a report).
This is the solution I wish I'd come up with ;)
Mar 9 '07 #10

Rabbit
Expert Mod 10K+
P: 12,366
That's exactly where I got it from too. I had to play around with it a bit putting it in different places in the SQL till I found one that worked
Mar 9 '07 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
Lucky I added the (I think) bit in there.
Nice one Rabbit. I thought of this thread when I saw your post in the other thread (Adding a Rank column to a report).
This is the solution I wish I'd come up with ;)
Me too!

Great work Rabbit.

Mary
Mar 9 '07 #12

ADezii
Expert 5K+
P: 8,638
Expand|Select|Wrap|Line Numbers
  1. SELECT Temp.State_Name, Temp.City_Name, Temp.Population
  2. FROM Table1 AS Temp
  3. WHERE City_Name In(SELECT TOP 5 City_Name FROM Table1 WHERE State_Name = Temp.State_Name ORDER BY Population DESC;);
Just replace Table1 with the name of your table.
You rascally Rabbit! Nice job.
Mar 9 '07 #13

Rabbit
Expert Mod 10K+
P: 12,366
Thanks. Quite proud of it myself lol.
Mar 9 '07 #14

P: 2
Hi, I'm the OP. Thanks for the helpful ideas, everyone!
Apr 18 '07 #15

Rabbit
Expert Mod 10K+
P: 12,366
Not a problem. Good to hear from you again.
Apr 18 '07 #16

Post your reply

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