Re: How ad an increasing index to a query result? - Mailing list pgsql-general

On 14 Oct 2009, at 19:05, Josip wrote:

> Hello,
>
> Could somebody please try to help me with this problem?

> 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


Short of enumerating those results in your application, the easiest
approach is probably to wrap your query in a join with generate_series
like so:

SELECT a, s.b
FROM (
    SELECT a
    FROM table1
    ORDER BY a DESC LIMIT 5
) AS t1, generate_series(5, 1, -1) AS s(b)

Alban Hertroys

--
Screwing up is the best way to attach something to the ceiling.


!DSPAM:737,4adaee3411688629581426!