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@xxxxxxxxx> 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@xxxxxxxxxxxxxx) > 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
