To: Randall Lucas <rlucas@tercent.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Ordinal value of row within set returned by a query? 
In-reply-to: <9AE98E9A-70FE-11D7-9BCD-000A957653D6@tercent.net> 
References: <9AE98E9A-70FE-11D7-9BCD-000A957653D6@tercent.net>
Comments: In-reply-to Randall Lucas <rlucas@tercent.net>
	message dated "Thu, 17 Apr 2003 14:01:25 -0400"
Date: Thu, 17 Apr 2003 15:35:41 -0400
Message-ID: <16894.1050608141@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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