Re: Different order by behaviour depending on where clause?

Hello

2011/10/28 Jan Bakuwel <jan(dot)bakuwel(at)greenpeace(dot)org>:
> 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
>

Look on EXPLAIN - these queries will have a different execution plan

http://www.postgresql.org/docs/8.4/static/sql-explain.html

Regards

Pavel Stehule

>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>