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.

score 6 · Accepted Answer · answered Jul 12 '14 at 01:46

6

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

answered Jul 12 '14 at 01:46

Gordon Linoff

1,242,037
58
646
786

Worked prefectly, thank you. Could you possibly look at my next question since you solved 1/4 of that one? http://stackoverflow.com/questions/24719716/sql-complex-sum-from-multiple-tables – danihodovic Jul 13 '14 at 05:05

score 2 · Answer 2 · answered Jul 12 '14 at 06:27

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

answered Jul 12 '14 at 06:27

CL.

173,858
17
217
259

FuzzyTree · Answer 3 · 2014-07-11T23:54:30.470

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

SQL: Filter rows with max value

3 Answers3

Linked

Related