Re: Probably simple answer

Andrew Gould writes:

> I think he simply wants the rows of the result set
> numbered from 1 to whatever the last row is. This is
> useful for referencing rows when discussing reports.
> It is also an immense help when calculating a record's
> statistical percentile ranking.
>
> Doing it in a table is no problem. But how do you
> create one dynamically in a select query?

It's not easily possible.

Even if you were to write a server-side function that would implement such
a counter, there is no guarantee that the rows would be returned in the
order that the function is called in. (Depending on how you express the
query it can be very likely, but there is no guarantee.)

It gets worse if you use ORDER BY or DISTINCT. Then the problem amounts
to adding a column to the result set after sorting, which is not possible
in the SQL query model.

So by far the easiest choice is to keep this counter in the client
application as you loop through the result set.

--
Peter Eisentraut peter_e(at)gmx(dot)net