[Fwd: Re: [Bacula-devel] SQL]

[Jon P. <Jo...@in...>]

> Hello Dan,
>
> 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


This is a totally broken sql implementation to allow this since you have
not specified anything to aggregate jobid with. eg: what happened to
jobid=1 and fileindex=1 ?

Databases which just discard data like this are broken - period.

A correct implementation will insist the sql statement contain some sort
of aggregation on the columns not being grouped by.

max, min, avg, etc would all be acceptable aggregation functions.

I think you will find if you do this, the sql will work in postgres,
sqlserver and other databases which do not allow ambiguous syntax.





>
> 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?
>
> --
> Best regards,
>
> Kern
>
>   (">
>   /\
>   V_V
>
>
> -------------------------------------------------------
> SF.Net email is sponsored by: Discover Easy Linux Migration Strategies
from IBM. Find simple to follow Roadmaps, straightforward articles,
informative Webcasts and more! Get everything you need to get up to
speed, fast. http://ads.osdn.com/?ad_id=7477&alloc_id=16492&op=click
_______________________________________________
> Bacula-devel mailing list
> Bac...@li...
> https://lists.sourceforge.net/lists/listinfo/bacula-devel
>


Jon Pounder

   _/_/_/  _/    _/  _/       _/_/_/  _/    _/  _/_/_/_/
    _/    _/_/  _/  _/         _/    _/_/  _/  _/_/
   _/    _/  _/_/  _/         _/    _/  _/_/  _/
_/_/_/  _/    _/  _/_/_/_/ _/_/_/  _/    _/  _/_/_/_/


Inline Internet Systems Inc.
Thorold, Ontario, Canada

Tools to Power Your e-Business Solutions
www.inline.net
www.ihtml.com
www.ihtmlmerchant.com
www.opayc.com



Jon Pounder

   _/_/_/  _/    _/  _/       _/_/_/  _/    _/  _/_/_/_/
    _/    _/_/  _/  _/         _/    _/_/  _/  _/_/
   _/    _/  _/_/  _/         _/    _/  _/_/  _/
_/_/_/  _/    _/  _/_/_/_/ _/_/_/  _/    _/  _/_/_/_/


Inline Internet Systems Inc.
Thorold, Ontario, Canada

Tools to Power Your e-Business Solutions
www.inline.net
www.ihtml.com
www.ihtmlmerchant.com
www.opayc.com