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" <mitch@venux.net>
To: <pgsql-general@postgresql.org>
Sent: Tuesday, February 13, 2001 5:54 PM
Subject: 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 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

"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