Thread: Ordinal value of row within set returned by a query?
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
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
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
>