On 18/02/2011 7:28 PM, Dan Langille wrote: > On 2/17/2011 8:49 PM, Jim Barber wrote: >> On 17/02/2011 6:54 PM, Torsten Maus wrote: >>> >>> My idea is that I copy the LAST Full Backups of ALL Clients to Tape. >>> Hence, since I am not familiar with the SQL commands so well, I am >>> looking for support from your side for such a pattern >>> >>> The only "rules" are: >>> >>> - I need that last Full Backup of any client, this shall be copied. >>> Thats it ? >>> >>> Can somebody help me with the correct SQL Query syntax ? >>> >> >> I use the following SQL to achieve that: >> >> SELECT MAX(Job.JobId) FROM Job, Pool WHERE Job.Level = 'F' and Job.Type >> = 'B' and Job.JobStatus = 'T' and Pool.Name = 'FullPool' and Job.PoolId >> = Pool.PoolId GROUP BY Job.Name ORDER BY Job.JobId; >> >> You'll probably want to change 'FullPool' to the name of the pool where >> you are directing your full backups to. >> >> If the pool you are selecting from doesn't matter then you could >> probably simplify the SQL to be as follows: >> >> SELECT MAX(Job.JobId) FROM Job WHERE Job.Level = 'F' and Job.Type = 'B' >> and Job.JobStatus = 'T' GROUP BY Job.Name ORDER BY Job.JobId; > > FYI, this is non-standard SQL. > > bacula=# SELECT MAX(Job.JobId) FROM Job WHERE Job.Level = 'F' and Job.Type = > 'B' and Job.JobStatus = 'T' GROUP BY Job.Name ORDER > BY Job.JobId; > ERROR: column "job.jobid" must appear in the GROUP BY clause or be used in > an aggregate function > LINE 1: ...nd Job.JobStatus = 'T' GROUP BY Job.Name ORDER BY Job.JobId; > ^ > bacula=# > > It works under MySQL because MySQL is doing stuff for you under the covers.
Just to finish this topic of with a correct answer... I was using SQLite3 which worked with the statement I had. However I've been working on moving over from SQLite3 to PostgreSQL now that I've read that the use of SQLite3 is deprecated. The correct statement to the one above is: SELECT MAX(Job.JobId) FROM Job WHERE Job.Level = 'F' and Job.Type = 'B' and Job.JobStatus = 'T' GROUP BY Job.Name ORDER BY MAX(Job.JobId); The only part that has changed is using the MAX() aggregate function in the ORDER BY clause. This works in both SQLite3 and PostgreSQL (and I'd assume MySQL, but I haven't tested). Regards, ------------------------------------------------------------------------------ Colocation vs. Managed Hosting A question and answer guide to determining the best fit for your organization - today and in the future. http://p.sf.net/sfu/internap-sfd2d _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
