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