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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
