[GENERAL] Re: Order question
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" [EMAIL PROTECTED] To: [EMAIL PROTECTED] 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: [GENERAL] 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 [EMAIL PROTECTED] 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: [GENERAL] Re: Order question
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: [GENERAL] Re: Order question
"Mitch Vincent" [EMAIL PROTECTED] 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