Re: [Bacula-users] SQL Query Copy Jobs show last Full

[Jim B. <jim...@dd...>]

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,