SQL: Filter rows with max value

An efficient way to do this is often to use not exists:

select t.*from table twhere not exists (select 1                  from table t2                  where t2.file = t.file and t2.Version > t.version                 );

This query can take advantage of an index on table(file, version).

This rephrases the query to be: "Get me all rows from the table where the corresponding file has no larger version."

In SQLite 3.7.11 or later, when you use MAX, the other values are guaranteed to come from the row with the largest value:

SELECT File,       MAX(Version) AS Version,       FunctionFROM MyTableGROUP BY File

Note that this will return multiple rows per file if the overall latest version for a file exists for different functions. i.e. if your example above had an additional row (1,3,2) this would return 2 rows for file 1.

select    t1.file,    t1.version,    t1.functionfrom     mytable t1join (    select         t2.file,        max(t2.version) max_version            from  mytable t2    group by t2.file) t3 join t1.file = t3.file and t1.version = t3.max_version