Re: Funny results in query

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Chris Pizzo <chris(at)artinside(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Funny results in query
Date: 2003-01-30 23:26:26
Message-ID: 520.1043969186@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Jonathon Batson 2003-01-30 23:27:14 Re: Developing a forms based web application
Previous Message Andres Ledesma 2003-01-30 21:03:19 where is libpq.so