pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedOrdinal value of row within set returned by a query?
3+ messages / 2 participants
[nested] [flat]
* Ordinal value of row within set returned by a query?
@ 2003-04-17 18:01 Randall Lucas <rlucas@tercent.net>
2003-04-17 19:35 ` Re: Ordinal value of row within set returned by a query? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Randall Lucas @ 2003-04-17 18:01 UTC (permalink / raw)
To: pgsql-sql
Hi folks,
I'm puzzling over whether it is possible within SQL alone to determine
the ordinal position of a row within the set returned by a query. It
seems clear to me that pgsql "knows" what position in a set a
particular tuple holds, since one can OFFSET, ORDER BY, and LIMIT;
however, I can't seem to find a function or "hidden field" that will
return this.
What I would like is something along these lines: I wish to ORDER BY
an ordinal field that is likely to be present, but may not be present,
and then by a unique value to ensure stability of ordering. Since the
ordinal may be absent, I'd like a running total on the side: For
example:
guys
name age
-------------------
bob 33
charlie 35
doug 28
ed 33
select name, age, running_total() from guys order by age, name
name age running_total()
-----------------------------------------
doug 28 1
bob 33 2
ed 33 3
charlie 35 4
I think I could do this by means of creating a temporary table within a
plpgsql function, but that seems awful heavy-duty. I have an inkling
that there may be an existing pg_ function or field, like oid, which I
might call to get this info. Thoughts?
Regards,
Randall
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Ordinal value of row within set returned by a query?
2003-04-17 18:01 Ordinal value of row within set returned by a query? Randall Lucas <rlucas@tercent.net>
@ 2003-04-17 19:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-04-17 20:50 ` Re: Ordinal value of row within set returned by a query? Randall Lucas <rlucas@tercent.net>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2003-04-17 19:35 UTC (permalink / raw)
To: Randall Lucas <rlucas@tercent.net>; +Cc: pgsql-sql
Randall Lucas <rlucas@tercent.net> writes:
> I'm puzzling over whether it is possible within SQL alone to determine
> the ordinal position of a row within the set returned by a query. It
> seems clear to me that pgsql "knows" what position in a set a
> particular tuple holds, since one can OFFSET, ORDER BY, and LIMIT;
> however, I can't seem to find a function or "hidden field" that will
> return this.
That's because there isn't one.
The traditional hack for this has been along the lines of
create temp sequence foo;
select nextval('foo'), * from
(select ... whatever ... order by something) ss;
drop sequence foo;
which is illegal per the SQL spec (you can't ORDER BY in a subselect
according to spec), but it's the only way that you can do computation
after a sort pass. In a single-level SELECT, ORDER BY happens after
the computation of the SELECT output values.
Usually it's a lot easier to plaster on the row numbers on the client
side, though.
> What I would like is something along these lines: I wish to ORDER BY
> an ordinal field that is likely to be present, but may not be present,
> and then by a unique value to ensure stability of ordering.
Why don't you order by the ordinal field, then the table's primary key?
(If it hasn't got a primary key, maybe it should.)
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Ordinal value of row within set returned by a query?
2003-04-17 18:01 Ordinal value of row within set returned by a query? Randall Lucas <rlucas@tercent.net>
2003-04-17 19:35 ` Re: Ordinal value of row within set returned by a query? Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-04-17 20:50 ` Randall Lucas <rlucas@tercent.net>
0 siblings, 0 replies; 3+ messages in thread
From: Randall Lucas @ 2003-04-17 20:50 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Thank you, and please keep up the excellent awesome brilliant work on
the amazing product which is pgsql.
On Thursday, April 17, 2003, at 03:35 PM, Tom Lane wrote:
> Randall Lucas <rlucas@tercent.net> writes:
>> I'm puzzling over whether it is possible within SQL alone to determine
>> the ordinal position of a row within the set returned by a query. It
>> seems clear to me that pgsql "knows" what position in a set a
>> particular tuple holds, since one can OFFSET, ORDER BY, and LIMIT;
>> however, I can't seem to find a function or "hidden field" that will
>> return this.
>
> That's because there isn't one.
>
> The traditional hack for this has been along the lines of
>
> create temp sequence foo;
>
> select nextval('foo'), * from
> (select ... whatever ... order by something) ss;
>
> drop sequence foo;
>
> which is illegal per the SQL spec (you can't ORDER BY in a subselect
> according to spec), but it's the only way that you can do computation
> after a sort pass. In a single-level SELECT, ORDER BY happens after
> the computation of the SELECT output values.
>
> Usually it's a lot easier to plaster on the row numbers on the client
> side, though.
>
>> What I would like is something along these lines: I wish to ORDER BY
>> an ordinal field that is likely to be present, but may not be present,
>> and then by a unique value to ensure stability of ordering.
>
> Why don't you order by the ordinal field, then the table's primary key?
> (If it hasn't got a primary key, maybe it should.)
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-04-17 20:50 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-17 18:01 Ordinal value of row within set returned by a query? Randall Lucas <rlucas@tercent.net>
2003-04-17 19:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-04-17 20:50 ` Randall Lucas <rlucas@tercent.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox