Thread: Order question

Order question

From
"Mitch Vincent"
Date:
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

From
"Mitch Vincent"
Date:
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

From
Mike Castle
Date:
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

From
Lamar Owen
Date:
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

From
"Mitch Vincent"
Date:
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

From
Tom Lane
Date:
"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

From
"Joseph"
Date:
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
> >
>