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?

score 12 · Answer 1 · edited Apr 19 '22 at 11:43

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.

score 1 · Accepted Answer · answered Jan 18 '18 at 17:23

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

score 0 · Answer 3 · answered Jul 15 '22 at 15:52

RANK or ROW_NUMBER window functions

Although max is guaranteed to work on SQLite as mentioned at https://stackoverflow.com/a/48328243/895245 the following method appears to be more portable and versatile:

SELECT *
FROM (
    SELECT
      ROW_NUMBER() OVER (
        PARTITION BY "name"
        ORDER BY "population" DESC
      ) AS "rnk",
      *
    FROM "table"
    WHERE "name" IN ('a', 'b', 'c')
  ) sub
WHERE
  "sub"."rnk" = 1
ORDER BY
  "sub"."name" ASC,
  "sub"."population" DESC

That exact same code works on both:

SQLite 3.34.0
PostgreSQL 14.3

Furthermore, we can easily modify that query to cover the following use cases:

if you replace ROW_NUMBER() with RANK(), it returns all ties for the max if more than one row reaches the max
if you replace "sub"."rnk" = 1 with "sub"."rnk" <= n you can get the top n per group rather than just the top 1

row with max value per group - SQLite

3 Answers3

Linked