Postgres Pro
Downloads
Home   >   mailing lists

Thread: Autoincrement value in a SELECT statement

Autoincrement value in a SELECT statement

From
JORGE MALDONADO
Date:
Hi,

Let's suppose that I have a table like this:

team_name       varchar(120)
score                integer

I have a "SELECT team_name, score FROM teams ORDER BY score DESC". This mean that the first position belongs to the highest score, second position to the next score, and so on.What I need is to have the position.

position 1 for the highesst score
position 2 for the next score
.
.
.

Is it possible to include a field in the SELECT statement which "kind of autoincrements" so I can get the position?

Respectfully,
Jorge Maldonado

Re: Autoincrement value in a SELECT statement

From
Tom Lane
Date:
JORGE MALDONADO <jorgemal1960@gmail.com> writes:
> Is it possible to include a field in the SELECT statement which "kind of
> autoincrements" so I can get the position?

The row_number() window function should do that for you, or you might
want rank() depending on what you think should happen for ties.
        regards, tom lane