CODEWITHSUNDEEP
sqlms-accessmax
Metabinary
Metabinary

Reputation: 187

SQL return data related to the corresponding MAX() value

I´ve a problem with a SQL query. My database look like this:

State   | City         | Population
-----------------------------------
Texas   | Austin       | 865.00
Texas   | Houston      | 2.200.00
Florida | Jacksonville | 840.000
Florida | Miami        | 417.000
...     | ...          | ...

Now i want to know the maximum population of a state and the related city like this:

OUTPUT:
 State   | City         | Population
 -----------------------------------
 Texas   | Houston      | 2.200.00
 Florida | Jacksonville | 840.000
 ...     | ...          | ...

How can i do this?

EDIT: I use MSAcces 2013, Table name: TPopulation. Sorry for the confusion.

Upvotes: 0

Views: 63

Answers (3)

Pittsburgh DBA
Pittsburgh DBA

Reputation: 6772

Note that this will return multiple cities per state, if the max population is the same for more than one city per state.

MS ACCESS:

SELECT 
  T1.State,
  T1.City,
  T1.Population
FROM
  TPopulation AS T1
WHERE
  T1.Population =
    (SELECT MAX(T2.Population) FROM TPopulation AS T2 WHERE T2.State = T1.State)

ANSI:

SELECT
    MaxP.State,
    S.City,
    S.Population
FROM
    (
    SELECT
        State,
        MAX(Population)
    FROM
        TPopulation
    GROUP BY
        State
    ) AS MaxP
    INNER JOIN TPopulation AS S ON
        S.State = MaxP.State
        AND S.Population = MaxP.Population
ORDER BY
    MaxP.State ASC,
    S.City ASC

Upvotes: 0

Metabinary
Metabinary

Reputation: 187

This is my code. It shows the right order, the biggest city first in each state. But it shows every city in each state and I only want the biggest city in each state:

SELECT State, City, Population
FROM TPopulation
ORDER BY State DESC , Population DESC;

OUTPUT:
State   | City         | Population
-----------------------------------
Texas   | Houston      | 2.200.00
Texas   | Austin       | 865.00
Florida | Jacksonville | 840.000
Florida | Miami        | 417.000
...     | ...          | ...

Upvotes: 0

shawnt00
shawnt00

Reputation: 17915

This method allows for ties although those are pretty unlikely here.

-- overall
select t1.State, t1.City, t1.Population
from T as t1
where Population = (
    select max(t2.Population) from T as t2
)

-- per state
select t1.State, t1.City, t1.Population
from T as t1
where t1.Population = (
    select max(t2.Population) from T as t2 where t2.State = t1.State
)

Upvotes: 2

Related Questions