row with max value per group - SQLite

Question

Given a table with columns(name, lat, lon, population, type) where there are many rows for each name, I'd like to select the rows grouped by name where population is the highest. The following works if I restrict myself to just name and population

SELECT name, Max(population) 
FROM table WHERE name IN ('a', 'b', 'c') 
GROUP BY name;

But I want the other columns — lat, lon, type — as well in the result. How can I achieve this using SQLite?

Answer 1

SQLite allows you to just list the other columns you want; they are guaranteed to come from the row with the maximum value:

SELECT name, lat, lon, Max(population), type
FROM table
WHERE name IN ('a', 'b', 'c')
GROUP BY name;

The docs read:

Special processing occurs when the aggregate function is either min() or max(). Example:

SELECT a, b, max(c) FROM tab1 GROUP BY a;

When the min() or max() aggregate functions are used in an aggregate query, all bare columns in the result set take values from the input row which also contains the minimum or maximum.

Answer 2

Join against that result to get the complete table records

SELECT t1.*
FROM your_table t1
JOIN 
(
    SELECT name, Max(population) as max_population
    FROM your_table 
    WHERE name IN ('a', 'b', 'c') 
    GROUP BY name
) t2 ON t1.name = t2.name
    and t1.population = t2.max_population