0

I'm writing simple backup program using sqlite using this layout: enter image description here

Each file is is identified by unique hash and it has multiple asociated records in file_version. When snapshot of database is created, most current file_versions are asociated with it via snapshot_file.

Example:

file (hash,path)

abc|/img.png
bcd|/img.jpeg

file_version (id,mtime,md5,hash)

1|1000|md5aoeu|abc
2|1500|md5bcda|abc
3|2500|md5asdf|abc
4|2500|md5aoaa|bcd

snapshot (time, description)

1250| 'first snapshot'
2000| 'second snapshot'
3000| 'third snapshot'

When I'm trying to create new snapshot, I need to query newest file_versions for each file and add appropriate records into snapshot_file. So If I were to create new snapshot, I would need id of newest file version of file with hash 'abc' (matching file /img.png).

So expected return of select for this query is:

3|2500|abc
4|2500|bcd

Sorry, my english is pretty bad (title might be confusing), if you need further clarification, please lemme know. Thanks in advance.

This is similar to: How can I select all entries with the highest version? however it's slightly more complicated than that (since there can be only one id per each file).

Community
  • 1
  • 1
Tomas Pruzina
  • 8,397
  • 6
  • 26
  • 39

1 Answers1

1

I would try something like this:

SELECT i.* 
FROM file_versions i
INNER JOIN (
    SELECT
      hash,
      MAX(mtime) AS latestTime
    FROM file_versions
    GROUP BY hash
)latest ON i.mtime = latest.latestTime
           and i.hash = latest.hash  

EDIT

Based on the OP's comment, I would change the code to use a CTE

WITH latest_CTE AS (hash, latestTime)
   SELECT
      hash,
      MAX(mtime) AS latestTime
    FROM file_versions
    GROUP BY hash
)
SELECT i.* FROM file_version i
JOIN latest_CTE c on i.mtime = c.latestTime
AND i.hash = c.hash

Common Table Expressions will give you improved performance even across millions of records. Please ensure that you have the right indexes on your table(s) though

Chris
  • 5,040
  • 3
  • 20
  • 24