Re: selecting un-ordered rows - Mailing list pgsql-novice

From Oscar Rodriguez Fonseca
Subject Re: selecting un-ordered rows
Date
Msg-id 20060518131346.698add49@vrlap.localvrnet
Whole thread Raw
In response to selecting un-ordered rows  (Dušan PEŠL <pesl@asprkyjov.cz>)
List pgsql-novice
El día Tue, 16 May 2006 13:34:33 +0200
Dušan PEŠL <pesl@asprkyjov.cz> escribió:

> have enyone idea, how to select all rows, preceeding 'mary'
> (first two rows, but not LIMIT 2)
>
> name     date
> albert   2006-05-17
> john     2006-05-17
> mary     2006-05-17
> benjamin 2006-05-17
> carl     2006-05-17
> alice    2006-05-17

You are implying that data is pre-ordered within the database, which IMHO is
incorrect in any relational database. If "un-ordered" means for you that is
ordered by date of insertion you might try adding a timestamp or serial
column to your table and perform a select like:

SELECT * FROM table ORDER BY tstamp LIMIT 2

If you do not want to add another column, you might try ordering by
oid "SELECT * FROM table ORDER BY oid LIMIT 2", provided you created
your table with oids (which I think is the default setting) but the
result is not guaranteed for a long lived database because oids can
roll-over.

You may want to have a look at:
http://www.postgresql.org/docs/8.1/static/queries-order.html
http://www.postgresql.org/docs/8.1/static/queries-limit.html

If you do not want to use limit you can use a WHERE clause instead. E.g.

SELECT * FROM table WHERE tstamp < (SELECT tstamp FROM table WHERE name = 'Mary')

Regards,

--
Oscar

pgsql-novice by date:

Previous
From: Sean Davis
Date:
Subject: Re: select count(*) and limit
Next
From: Oscar Rodriguez Fonseca
Date:
Subject: Re: select count(*) and limit