I found an article that should help you with the answer:
http://explainextended.com/2009/05/05/postgresql-row-numbers/
ROWNUM is a very useful pseudocolumn in Oracle that returns the
position of each row in a final dataset.
Upcoming PostgreSQL 8.4 will have this pseudocolumn, but as for now
will we need a hack to access it. (in 8,3)
The main idea is simple:
1. Wrap the query results into an array
2. Join this array with a generate_series() so that numbers from 1
to array_upper() are returned
3. For each row returned, return this number (as ROWNUM) along the
corresponding array member (which is the row from the original query)
...
See original article for the code
Hope it helps
Andy Bailey
On Wed, Oct 14, 2009 at 12:05 PM, Josip <josip.2000@gmail.com> wrote:
> Hello,
>
> Could somebody please try to help me with this problem?
> So, let’s say that I have the query:
>
> CREATE SEQUENCE c START 1;
>
> SELECT a, nextval('c') as b
> FROM table1
> ORDER BY a DESC LIMIT 5;
>
> I.e., I want to pick the 5 largest entries from table1 and show them
> alongside a new index column that tells the position of the entry. For
> example:
>
> a | b
> --------
> 82 | 5
> 79 | 4
> 34 | 3
> 12 | 2
> 11 | 1
>
> However, when I try this approach, the values of column b don’t follow
> the correct order. How should I go about and modify my code?
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
Andrew Bailey
(312) 866 9556
NOTA DE CONFIDENCIALIDAD Y DE NO DIVULGACIÓN:
La información contenida en este E-mail y sus archivos adjuntos es
confidencial y sólo puede ser utilizada por el individuo
o la empresa a la cual está dirigido. Si no es el receptor autorizado,
cualquier retención, difusión,
distribución o copia de este mensaje queda prohibida y sancionada por
la ley. Si por error recibe este
mensaje, favor devolverlo y borrarlo inmediatamente.
