Finding rows with the maximum

Question

Hi - I have a pretty simple table in SQLite, with the following schema:

CREATE TABLE IF NOT EXISTS Palettes 
             (id INTEGER PRIMARY KEY AUTOINCREMENT, 
             class TEXT, count INTEGER, name TEXT);

These represent color palettes, and several palettes can have the same name, but different counts (i.e. sizes).

What I want to do is find, for each set of named palettes, the one having the greatest (or least) count.

My first thought was to start with:

SELECT * FROM Palettes GROUP BY name;

But, of course, which row I get is arbitrary. Looking further, it seems that even if I do:

SELECT MAX("count"), * FROM Palettes GROUP BY name;

I still get an arbitrary row. One last shot:

SELECT * FROM (SELECT * FROM Palettes ORDER BY "count") GROUP BY name;

seems to work, but I cant find any guarantees anywhere.

Does someone have a solution to this problem? I can, of course, solve it in code, but I'd prefer an SQL solution, if possible.

Thanks, -matt

Answer 1

This should do the trick:

SELECT P.*
  FROM Palettes AS P JOIN
       (SELECT name, MAX("count") AS max_count
          FROM Palette
         GROUP BY Name) AS MC
       ON MC.Name = P.Name AND P."count" = MC.Max_Count;

If there are several rows with the same maximum count for a particular name, you will get several rows returned.

I'm using double quotes around "count" because it is, of course, also a keyword; the double quotes convert the name into a delimited identifier.