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.

-- 
Dan Langille - http://langille.org/

------------------------------------------------------------------------------
The ultimate all-in-one performance toolkit: Intel(R) Parallel Studio XE:
Pinpoint memory and threading errors before they happen.
Find and fix more than 250 security defects in the development cycle.
Locate bottlenecks in serial and parallel code that limit performance.
http://p.sf.net/sfu/intel-dev2devfeb
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users