Обсуждение: increment row number function question

Hello. I need a way to return an iterator result as a column.... eg,

SELECT increment() as ii, some_col from some_tbl order by some_col desc limit 50;

 ii | some_col
----+----------
  1 |       zest
  2 |       test
  3 |       nest
  4 |       fest
[...]

How can I acheive those results? Thanks for any help.

---

How low will we go? Check out Yahoo! Messenger’s low PC-to-Phone call rates.

Matthew Peter <survivedsushi@yahoo.com> writes:
> Hello. I need a way to return an iterator result as a column.... eg,

> SELECT increment() as ii, some_col from some_tbl order by some_col desc limit 50;

>  ii | some_col
> ----+----------
>   1 |       zest
>   2 |       test
>   3 |       nest
>   4 |       fest
> [...]

You'd be a whole lot better off to attach the row numbers in your
client-side code.

Even if such a function existed, it would almost certainly not do what
you want in this query.  Per SQL spec, the SELECT target list is
logically supposed to be evaluated before the ORDER BY and LIMIT steps,
which means you'd get numbers associated with the physical ordering of
the rows not their some_col ordering.

You could possibly work around that problem with a sub-select, but at
some point you need to ask yourself whether it's not simpler to do a
presentation-oriented task like this in the client.

            regards, tom lane

Matthew Peter <survivedsushi@yahoo.com> writes:

> Hello. I need a way to return an iterator result as a column.... eg,
> SELECT increment() as ii, some_col from some_tbl order by some_col desc limit 50;

Use a sequence for the iterator.


--
Jorge Godoy      <jgodoy@gmail.com>

Thanks for the reply.

Even if such a function existed, it would almost certainly not do what
you want in this query. Per SQL spec, the SELECT target list is
logically supposed to be evaluated before the ORDER BY and LIMIT steps, which means you'd get numbers associated with the physical ordering of the rows not their some_col ordering.

Shucks. I wanted to return a set with an ordered iterator determined by the ORDER BY. Could that be done?

regards, tom lane

Thanks again.
 

---

Yahoo! Messenger with Voice. Make PC-to-Phone Calls to the US (and 30+ countries) for 2¢/min or less.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 10/07/06 18:47, Matthew Peter wrote:
>> Thanks for the reply. Even if such a function existed, it would
>> almost certainly not do what you want in this query.  Per SQL
>> spec, the SELECT target list is logically supposed to be
>> evaluated before the ORDER BY and LIMIT steps, which means you'd
>> get numbers associated with the physical ordering of the rows not
>> their some_col ordering.
>
> Shucks. I wanted to return a set with an ordered iterator
> determined by the ORDER BY. Could that be done? regards, tom lane

A stored procedure should do the trick, no?

- --
Ron Johnson, Jr.
Jefferson LA  USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFFKEPRS9HxQb37XmcRAiRrAJ4vVxbhzwwH/8vWoZHH76lI4dT9kACg5zvH
ufGPSbGV0wHCvmkCPWjiI1U=
=PUKR
-----END PGP SIGNATURE-----