CODEWITHSUNDEEP
sql
woodings
woodings

Reputation: 7693

How write SQL to select the maximum value in each group?

Let's say we have a table of State (varchar), City (varchar) and population (int) of the city like this,

New York, New York, 8175133
Los Angeles, California, 3792621
Chicago, Illinois, 2695598
San Diego, California, 1307402

I wanted to select the city of each state which has the largest population. The result would be,

New York, New York, 8175133
Los Angeles, California, 3792621
Chicago, Illinois, 2695598

How to write the SQL to do this?

UPDATE:

let me make it clear: the results should contain one city per state which has largest population. In the example above, 'San Diego' has less population than 'Los Angeles' so it won't show up in the results.

Upvotes: 2

Views: 3937

Answers (3)

Magnus Lyckå
Magnus Lyckå

Reputation: 11

select *
from pop 
where (state, population) in 
   (select state, max(population) 
    from pop 
    group by state);

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

In the rare case where there is a tie with 2 or more cities having same population, both will be shown:

SELECT 
    t.*
FROM 
    tableX  t
  JOIN
    ( SELECT State
           , MAX(Population) AS Population 
      FROM tableX
      GROUP BY State
    ) maxp  
        ON  maxp.State = t.State
        AND maxp.Population = t.Population

If your DBMS has analytic (window) functions, you can also use this (with ties shown, as previously):

SELECT 
    City
  , State
  , Population
FROM 
    ( SELECT City
           , State
           , Population
           , RANK() OVER ( PARTITION BY State 
                           ORDER BY Population DESC )
               AS RankN
      FROM tableX
    ) maxp  
WHERE RankN = 1 ;

or this (ties resolved, only one row per state returned):

SELECT 
    City
  , State
  , Population
FROM 
    ( SELECT City
           , State
           , Population
           , ROW_NUMBER() OVER ( PARTITION BY State 
                                 ORDER BY Population DESC 
                                        , City ASC )
               AS RowN
      FROM tableX
    ) maxp  
WHERE RowN = 1 ;

Test in SQL-Fiddle

Upvotes: 8

pcsi
pcsi

Reputation: 604

Just do it:

SELECT state, city, MAX(population) AS LargestPeople FROM yourtable GROUP BY state DESC

Upvotes: 0

Related Questions