SQL: Filter rows with max value

Question

This is my table structure:

File    |   Version     |   Function
1       |   1           |   1
1       |   2           |   1
1       |   3           |   1
1       |   2           |   2

2       |   1           |   4
3       |   2           |   5

I need it to return these rows only

1       |   3           |   1
2       |   1           |   4
3       |   2           |   5

Meaning I only want the functions that have the most recent version for each file.

I do not want the result below, i.e unique function ids that are not the most recent version

1       |   3           |   1
1       |   2           |   2
...

I've looked at How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?, but that returns the most recent unique function ids.

The query needs to be sqlite3 compatible.

Answer 1

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

select t.*
from table t
where 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."

Answer 2

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,
       Function
FROM MyTable
GROUP BY File