Re: Simulate count result are distinct between 8.3 and 8.4 - Mailing list pgsql-general

Emanuel Calvo Franco wrote:
> hi people!
>
> I have this test query to simulate rownums in 8.3:
>
> SELECT
> (select count(i)+1 from prueba
> where i < xi.i
> ) as rownum, i, p
> FROM prueba xi limit 5;
>
> Devuelve , que esta bien:
> 1;1;"299361"
> 2;2;"421127"
> 3;3;"166284"
> 4;4;"458945"
> 5;5;"81619"
>
>
> But in 8.4 throws this:
>
> postgres=# SELECT
> postgres-# (select count(i)+1 from prueba
> postgres(# where i < xi.i
> postgres(# ) as rownum, i, p
> postgres-# FROM prueba xi limit 5;
>  rownum |   i    |   p
> --------+--------+--------
>  168770 | 168763 | 908731
>  168771 | 168764 | 640826
>  168772 | 168765 | 571112
>  168773 | 168766 | 992462
>  168774 | 168767 | 992471
> (5 filas)

Without an order by in your query, the db can return them as soon as it
finds the rows.

If you add an order by (to the outer part), it should be more reliable.

--
Postgresql & php tutorials
http://www.designmagick.com/