Re: [Bacula-devel] Re: SQL

[Kern S. <ke...@si...>]

On Sunday 03 July 2005 11:42, Timm Reinstorf wrote:
> Kern Sibbald schrieb:
> > It seems that both MySQL and SQLite permit SQL statements that do what I
> > want. However, these SQL statements are illegal in PostgreSQL.  The
> > statement is of the form (somewhat simplified):
> >
> > select jobid, fileindex from job, ... where jobid in (1,2) ...
> >    group by fileindex;
> >
> > Now, if I have values as follows:
> >
> >   jobid    fileindex
> >    1           1
> >    2           1
> >    1           2
> >    1           3
> >    2           3
> >
> > Both MySQL and SQLite return,
> >
> >    2            1
> >    1            2
> >    2            3
> >
> > Both MySQL and SQL return only a single value for fileindex and the
> > value for jobid is always the last one in the "in" clause, if there are
> > duplicates.  This is what I want.
> >
> > What SQL statement in PostgreSQL would accomplish the same thing?
>
> Hello,
>
> maybe the following sql is the one you are looking for? To get the
> greatest jobid for each fileindex?
>
> select max(jobid), fileindex from job, ... where jobid in (1,2) ...
>      group by fileindex;
>
> If the jobid's are in increasing order in respect to the time the job
> runs. Otherwise, a sql join to the job records and a "max" on the time
> the job was run is needed?
>
> I'm not sure, if that is what you are looking for, but that should be
> valid sql.

Yes, I think this is exactly what I need -- probably best as you note to use 
max() of the starttime.  I'll experiment with this a bit to see if I can get 
what I want. 

Thanks.  This is a *big* help.

-- 
Best regards,

Kern

  (">
  /\
  V_V