SQL: Filter rows with max value
greatest-n-per-group, sql, sqlite
Solution
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."
Problem
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.