Different order by behaviour depending on where clause? - Mailing list pgsql-sql

From Jan Bakuwel
Subject Different order by behaviour depending on where clause?
Date
Msg-id 4EAA3550.7080609@greenpeace.org
Whole thread Raw
Responses Re: Different order by behaviour depending on where clause?  (Pavel Stehule <pavel.stehule@gmail.com>)
List pgsql-sql
Hi,

I have a compound query with some grouping, having and order by's saved
as a view, say with name "myview".

A) select * from "myview" returns the results as expected in the correct
order (the order by is on fields "Category", "Year", "Month" and a few
other fields). The results are correctly ordered by these fields as
specified in the view.

B) select * from "myview" where "Year"=2011 and "Month"=1 also returns
the results as expected in the correct order (a subset of A).

however

C) select * from "myview" where "Year"=2011 and "Month" >= 1 and "Month"
<= 1 returns the same resultset as B but the order of the rows is not
correct (not even close; haven't been able to see any pattern).

Any idea how I can further analyse/diagnose this?

regards,
Jan



pgsql-sql by date:

Previous
From: Tom Lane
Date:
Subject: Re: optimize self-join query
Next
From: Lee Hachadoorian
Date:
Subject: Re: optimize self-join query