Search Postgresql Archives

Re: How ad an increasing index to a query result?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux