Thread
-
Order question
Mitch Vincent <mitch@venux.net> — 2001-02-13T22:54:08Z
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 -
Re: Order question
Mitch Vincent <mitch@venux.net> — 2001-02-13T22:58:35Z
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 > > >
-
Re: Re: Order question
Mike Castle <dalgoda@ix.netcom.com> — 2001-02-13T23:18:16Z
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 -
Re: Re: Order question
Lamar Owen <lamar.owen@wgcr.org> — 2001-02-13T23:23:54Z
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
-
Re: Re: Order question
Mitch Vincent <mitch@venux.net> — 2001-02-13T23:23:55Z
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 >
-
Re: Re: Order question
Tom Lane <tgl@sss.pgh.pa.us> — 2001-02-13T23:53:49Z
"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
-
RE: Re: Re: Order question
Joseph <lters@mrtc.com> — 2001-02-16T11:24:36Z
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 > > >