1# Get the list of the last recent version per Delta with a given jobid list 2# This is a tricky part because with SQL the result of 3# 4# SELECT MAX(A), B, C, D FROM... GROUP BY (B,C) 5# 6# doesn't give the good result (for D). 7# 8# With PostgreSQL, we can use DISTINCT ON(), but with Mysql or Sqlite, 9# we need an extra join using JobTDate. 10SELECT FileId, 11 Job.JobId AS JobId, 12 FileIndex, 13 File.PathId AS PathId, 14 File.Name AS Name, 15 LStat, 16 MD5, 17 DeltaSeq, 18 Fhinfo, 19 Fhnode, 20 Job.JobTDate AS JobTDate 21FROM Job, 22 File, 23 24 (SELECT MAX(JobTDate) AS JobTDate, 25 PathId, 26 FileName 27 FROM 28 (SELECT JobTDate, 29 PathId, 30 File.Name AS FileName 31 FROM File 32 JOIN Job USING (JobId) 33 WHERE File.JobId IN (%s) 34 UNION ALL 35 SELECT JobTDate, 36 PathId, 37 File.Name AS FileName 38 FROM BaseFiles 39 JOIN File USING (FileId) 40 JOIN Job ON (BaseJobId = Job.JobId) WHERE BaseFiles.JobId IN (%s) ) AS tmp 41 GROUP BY PathId, 42 FileName) AS T1 43WHERE (Job.JobId IN 44 (SELECT DISTINCT BaseJobId 45 FROM BaseFiles 46 WHERE JobId IN (%s)) 47 OR Job.JobId IN (%s)) 48 AND T1.JobTDate = Job.JobTDate 49 AND Job.JobId = File.JobId 50 AND T1.PathId = File.PathId 51 AND T1.FileName = File.Name 52 53