On 4 aug 2006, at 08.55, Jonathan Sinclair wrote:
> SELECT field1, SUM(field2) as Field2, SUM(field3)
> FROM table1 tb1, table2 tb2
> WHERE tb1.field5 BETWEEN '03/07/2006' AND '03/08/2006'
> AND tb1.field6 = tb2.field7
> AND tb1.field8 = tb2.field8
> AND tb2.field9 BETWEEN 50.00 AND 150.00
> GROUP BY field1
> HAVING SUM(field2) BETWEEN 95.00 AND 100.00
> ORDER BY 2 DESC, field1;
>
> The results I get from Informix and mySql return identical results,
> however postgres includes a great deal more i.e. 11 rows from Informix
> and mySQL, 203 from postgres.
>
> Does anyone have any idea why this disparity may exist?
An obvious source of ambiguity is the date comparison:
tb1.field5 BETWEEN '03/07/2006' AND '03/08/2006'
Is that interval a day or a month (mm/dd/yyyy or dd/mm/yyyy)? Check
your datestyle setting and make sure all systems interpret the date
correctly (or at least the same).
Sincerely,
Niklas Johansson