CODEWITHSUNDEEP
sqlsqlitegreatest-n-per-group
danihodovic
danihodovic

Reputation: 1255

SQL: Filter rows with max value

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.

Upvotes: 5

Views: 8162

Answers (3)

CL.
CL.

Reputation: 180060

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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."

Upvotes: 6

FuzzyTree
FuzzyTree

Reputation: 32392

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.function
from 
    mytable t1
join (
    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

Upvotes: 1

Related Questions