Thread: Order question
Hey guys, another strange question here..
If I query and order by a field and there are duplicate values in that
field, what makes one return before the other? Just the first one that PG
comes to on the disk is displayed first or is something else looked at to
determine the order?
Example :
hhs=# SELECT ordernum,fieldname,oid from resultstyle WHERE style_id=1001
order by ordernum asc;
ordernum | fieldname | oid
----------+---------------+---------
1 | J.jobtitle | 1197126
1 | J.inv_id | 1197125
2 | J.updatedon | 1197127
3 | J.empinitials | 1197128
What makes the record with j.jobtitle appear above te other, when ordernum
is the same?
Thanks!
-Mitch
A further extension of this.. What might I be able to additionally order by so that the most recently updated rows get ordered above everything else (within the order by ordernum).. Using the same example : > ordernum | fieldname | oid > ----------+---------------+--------- > 1 | J.jobtitle | 1197126 > 1 | J.inv_id | 1197125 > 2 | J.updatedon | 1197127 > 3 | J.empinitials | 1197128 I just set the row with j.inv_id to 1, I'd like it to be ordered above the row with j.jobtitle in it -- is that possible? Thanks again! -Mitch ----- Original Message ----- From: "Mitch Vincent" <mitch@venux.net> To: <pgsql-general@postgresql.org> Sent: Tuesday, February 13, 2001 5:54 PM Subject: Order question > Hey guys, another strange question here.. > > If I query and order by a field and there are duplicate values in that > field, what makes one return before the other? Just the first one that PG > comes to on the disk is displayed first or is something else looked at to > determine the order? > > Example : > > hhs=# SELECT ordernum,fieldname,oid from resultstyle WHERE style_id=1001 > order by ordernum asc; > ordernum | fieldname | oid > ----------+---------------+--------- > 1 | J.jobtitle | 1197126 > 1 | J.inv_id | 1197125 > 2 | J.updatedon | 1197127 > 3 | J.empinitials | 1197128 > > > What makes the record with j.jobtitle appear above te other, when ordernum > is the same? > > Thanks! > > -Mitch > > >
On Tue, Feb 13, 2001 at 05:58:35PM -0500, Mitch Vincent wrote:
> I just set the row with j.inv_id to 1, I'd like it to be ordered above the
> row with j.jobtitle in it -- is that possible?
make a trigger that updates a time stamp and order by that?
mrc
--
Mike Castle Life is like a clock: You can work constantly
dalgoda@ix.netcom.com and be right all the time, or not work at all
www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc
We are all of us living in the shadow of Manhattan. -- Watchmen
Mitch Vincent wrote: > > A further extension of this.. > > What might I be able to additionally order by so that the most recently > updated rows get ordered above everything else (within the order by > ordernum).. Using the same example : > > > ordernum | fieldname | oid > > ----------+---------------+--------- > > 1 | J.jobtitle | 1197126 > > 1 | J.inv_id | 1197125 > > 2 | J.updatedon | 1197127 > > 3 | J.empinitials | 1197128 > > I just set the row with j.inv_id to 1, I'd like it to be ordered above the > row with j.jobtitle in it -- is that possible? Add a column with a timestamp. Then, in the update/insert, make the timestamp equal the current time. Then ORDER BY ordernum, timestamp. Or better, modify the other ordernums, as you have an ambiguous situation with two ordernums being equal. Writing that in a single UPDATE would be left as an exercise for the reader :-). -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
Sure, I was just wondering if there was some PG internal value I could use (and I should have said that in my email).. Thanks for the suggestion though, I appreciate it.. -Mitch ----- Original Message ----- From: "Mike Castle" <dalgoda@ix.netcom.com> To: <pgsql-general@postgresql.org> Sent: Tuesday, February 13, 2001 6:18 PM Subject: Re: Re: Order question > On Tue, Feb 13, 2001 at 05:58:35PM -0500, Mitch Vincent wrote: > > I just set the row with j.inv_id to 1, I'd like it to be ordered above the > > row with j.jobtitle in it -- is that possible? > > make a trigger that updates a time stamp and order by that? > > mrc > -- > Mike Castle Life is like a clock: You can work constantly > dalgoda@ix.netcom.com and be right all the time, or not work at all > www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc > We are all of us living in the shadow of Manhattan. -- Watchmen >
"Mitch Vincent" <mitch@venux.net> writes:
> I just set the row with j.inv_id to 1, I'd like it to be ordered above the
> row with j.jobtitle in it -- is that possible?
Not unless you add a field with an update sequence number, or some such,
and then explicitly use that field as a second ORDER BY key. The system
does not maintain anything like that for you --- and should not, IMHO,
since it'd be useless overhead for apps that didn't care.
regards, tom lane
I suppose you could use the oid column as long as it was not wrapping (starting to count over). Joseph > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Mitch Vincent > Sent: Tuesday, February 13, 2001 6:24 PM > To: Mike Castle; pgsql-general@postgresql.org > Subject: [GENERAL] Re: Re: Order question > > > Sure, I was just wondering if there was some PG internal value I could use > (and I should have said that in my email).. > > Thanks for the suggestion though, I appreciate it.. > > -Mitch > > ----- Original Message ----- > From: "Mike Castle" <dalgoda@ix.netcom.com> > To: <pgsql-general@postgresql.org> > Sent: Tuesday, February 13, 2001 6:18 PM > Subject: Re: Re: Order question > > > > On Tue, Feb 13, 2001 at 05:58:35PM -0500, Mitch Vincent wrote: > > > I just set the row with j.inv_id to 1, I'd like it to be ordered above > the > > > row with j.jobtitle in it -- is that possible? > > > > make a trigger that updates a time stamp and order by that? > > > > mrc > > -- > > Mike Castle Life is like a clock: You can work constantly > > dalgoda@ix.netcom.com and be right all the time, or not work at all > > www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc > > We are all of us living in the shadow of Manhattan. -- Watchmen > > >