Skip site navigation (1) Skip section navigation (2)

Re: Select query order

From: Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>
To: Francisco Leovey <fleovey(at)yahoo(dot)com>
Cc: pgsql <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Select query order
Date: 2010-03-24 12:29:47
Message-ID: 264855a01003240529n48c4b3cet922c7a471464743c@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
On Wed, Mar 24, 2010 at 8:21 AM, Francisco Leovey <fleovey(at)yahoo(dot)com> wrote:

> Use oid as the sort order
> SELECT *,oid from table order by oid
>
>
Hi, Francisco.  I hope you don't mind this going back to the list....

I wouldn't recommend this solution for several reasons including the fact
that OIDs are not by default created for user tables.  They are not
guaranteed to be unique and even the insert order is not accurately tracked
by them if there is a rollover.  See here for details:

http://www.postgresql.org/docs/8.4/static/datatype-oid.html

Sean



> --- On *Wed, 3/24/10, Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>* wrote:
>
>
> From: Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>
> Subject: Re: [NOVICE] Select query order
> To: "Krithinarayanan Ganesh Kumar" <krithinarayanan(at)gmail(dot)com>
> Cc: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
> Date: Wednesday, March 24, 2010, 9:13 AM
>
> On Wed, Mar 24, 2010 at 8:06 AM, Krithinarayanan Ganesh Kumar
> <krithinarayanan(at)gmail(dot)com<http://us.mc517.mail.yahoo.com/mc/compose?to=krithinarayanan(at)gmail(dot)com>>
> wrote:
> > Hi All,
> >
> > I am aware that Select query does not guarantee the order of the rows
> > returned ( The rows are returned in whatever order the system finds
> fastest
> > to produce).
> >
> > Is there any way to SELECT the rows in the same order of insertion ? The
> > problem is there is no Primary Key in the table, I am having only a
> > composite key. So I cannot ORDER BY pk also.
>
> Unless you have stored something in the table that correlates with the
> order of insertion, I don't know of a way to get at the insertion
> order as this information is not tracked by Postgresql.
>
> Sean
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org<http://us.mc517.mail.yahoo.com/mc/compose?to=pgsql-novice(at)postgresql(dot)org>
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>

In response to

pgsql-novice by date

Next:From: Zdravko BalordaDate: 2010-03-24 14:44:31
Subject: Table inheritance
Previous:From: A. KretschmerDate: 2010-03-24 12:22:11
Subject: Re: Select query order

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group