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

2009/10/14 Josip <josip(dot)2000(at)gmail(dot)com>:
> 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?

The problem here is that the sequence will merely act as a counter,
and knows nothing about the data you're using it to count against. In
the example you gave, it appears you're inserting data into the table
in ascending order, but you want it output in descending order.

If you're using PostgreSQL 8.4, you can do the following:

SELECT a, rank() OVER (ORDER BY a DESC)
FROM table1
ORDER BY a DESC

This is called a window function. It is using a window aggregate
function to rank column a in descending order (as declared in the OVER
clause).

If you are using an earlier version of Postgres, you could always use
a temporary table:

CREATE TEMP TABLE temp_table1
(
orderseq SERIAL PRIMARY KEY,
a INTEGER NOT NULL
) ON COMMIT DROP;

INSERT INTO temp_table1 (a)
SELECT a
FROM table1
ORDER BY a DESC;

SELECT orderseq, a
FROM temp_table1;

There may be another way, but can't think of one of the top of my head.

Thom