Sorting, when values are equal....

[Net Virtual Mailing Lists]

Hello,


If I do something like:

SELECT * FROM sometable ORDER BY somerow DESC LIMIT 1 OFFSET 2;

... and there are multiple rows in sometable where somerow is identical,
am I assured that the values will always come back in the same order?
...... Or do I need to ensure that a second sort (such as the oid?) is used?


Thanks!

- Greg




---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Martijn van Oosterhout]

Re: Sorting, when values are equal....

On Mon, Oct 25, 2004 at 04:45:15AM -0700, Net Virtual Mailing Lists wrote:[color=blue]
> Hello,
>
>
> If I do something like:
>
> SELECT * FROM sometable ORDER BY somerow DESC LIMIT 1 OFFSET 2;
>
> .. and there are multiple rows in sometable where somerow is identical,
> am I assured that the values will always come back in the same order?
> ..... Or do I need to ensure that a second sort (such as the oid?) is used?[/color]

No order in guarenteed or implied unless you specify it. So you'll need
another field to order by, generally the primary key works well..
--
Martijn van Oosterhout <kleptog@svana. org> http://svana.org/kleptog/[color=blue]
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.[/color]

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBfPW0Y5T wig3Ge+YRAtLUAK C+RrJCjizFMfkXy ukavx70P2M7BgCg kkOU
k+c0jm58qCot0J+ mzQV6gmo=
=c8vS
-----END PGP SIGNATURE-----

[Peter Eisentraut]

Re: Sorting, when values are equal....

Net Virtual Mailing Lists wrote:[color=blue]
> If I do something like:
>
> SELECT * FROM sometable ORDER BY somerow DESC LIMIT 1 OFFSET 2;
>
> .. and there are multiple rows in sometable where somerow is
> identical, am I assured that the values will always come back in the
> same order? .....[/color]

No.
[color=blue]
> Or do I need to ensure that a second sort (such as
> the oid?) is used?[/color]

Yes.

--
Peter Eisentraut

http://developer.postgresql.org/~petere/

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings