From: | Randall Lucas <rlucas(at)tercent(dot)net> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Ordinal value of row within set returned by a query? |
Date: | 2003-04-17 20:50:41 |
Message-ID: | 4025A0D0-7116-11D7-9BCD-000A957653D6@tercent.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)tercent(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Tzvetan Tzankov | 2003-04-18 09:06:10 | Deadlock problem |
Previous Message | Tom Lane | 2003-04-17 19:35:41 | Re: Ordinal value of row within set returned by a query? |