Re: Funny results in query

Chris Pizzo <chris(at)artinside(dot)com> writes:
> can someone explain why this is happening?

> select field1, field2, field3, field4, field5, field6, field7, field8,
> field9, field10, field11, field12, field13, field14 from table where
> field18 = 't' order by us_retail_price asc offset 48 limit 6;
> [returns different results from]
> select field1, field2, field3, field4, field5, field6, field7, field8,
> field9, field10 from table where field18 = 't' order by us_retail_price
> asc offset 48 limit 6;

Is 'order by us_retail_price' a unique ordering of the rows? (I'd bet
not.) If not, the server is entitled to sort rows of equal price
however it wants. It's an extremely bad idea to use limit/offset
without an order-by clause that *completely* determines the row
ordering, because otherwise the results are unspecified.

> it appears the number of fields i have in the select is affecting the
> results. I don't understand why?

Could have something to do with the number of rows that fit into
sort_mem at one time.

regards, tom lane