Help with top two city in each State

[hmho]

Hello,

I need help setting up query that will give me the top two city in population for each State. My table data look like this.

City State Population
Hartford CT 120,000
Bridgeport CT 110,000
Stamford CT 100,000
Waterbury CT 100,000
West Hartf CT 90,000
Boston MA 120,000
Springfield MA 110,000
Holyoke MA 100,000
Wooster MA 100,000
Winsted MA 90,000
Brooklyn NY 120,000
Ney York NY 110,000
Bronx, NY 100,000
Queens NY 100,000
Long Island NY 90,000

 

[boblarson]

You need something like this:

SELECT MyTable.State, MyTable.Population, MyTable.City
FROM MyTable
WHERE MyTable.Population IN(
      SELECT TOP 2 Population FROM MyTable As DUPE
      WHERE DUPE.State = MyTable.State
      ORDER BY DUPE.POPULATION DESC)
ORDER BY MyTable.State, MyTable.Population DESC , MyTable.City;

When I run it for me I get this:

 

[boblarson]

Sorry, gotta fix something. I didn't see that I had the same city listed twice.

 

[boblarson]

You have an exact number for one of the cities so it ends up with 3 instead of 2 because of the tie.

Normally, according to this page here, you would include the primary key so that you can break the ties. But as I don't know what you have for a key, I couldn't replicate it.

 

[hmho]

You need something like this:

SELECT MyTable.State, MyTable.Population, MyTable.City
FROM MyTable
WHERE MyTable.Population IN(
      SELECT TOP 2 Population FROM MyTable As DUPE
      WHERE DUPE.State = MyTable.State
      ORDER BY DUPE.POPULATION DESC)
ORDER BY MyTable.State, MyTable.Population DESC , MyTable.City;

When I run it for me I get this:

Boblarson,

What about if one city is there twice, and i want the sum of the population to be the top two

 

[boblarson]

Boblarson,

What about if one city is there twice, and i want the sum of the population to be the top two

That starts to get a bit beyond me for doing it in one query. In this case I would do a grouping query first with a sum for that field. Then you can use that query in place of the table in my example. Be sure to reference the query as dupe also.