How to add an ordinal id field based on a sort order in PostgreSQL

Question

I was wondering how to add a field to a table (e.g. country) based on sorting some of its fields (e.g. ISO code)?

I searched around SO and found the following question, but the solution is based on MySQL. I tried to use the solution there in Postgres 9.3 and it's giving me an syntax error on :=. What I tried is:

SELECT  l.*,
        @curRow := @curRow + 1 AS row_number
FROM    country l ORDER BY ISO
JOIN    (SELECT @curRow := 0) r;

How to adapt this to Postgres?

Possible duplicate: http://stackoverflow.com/questions/14394002/how-to-add-row-number-in-a-view — Erwin Brandstetter, Dec 21 '14 at 01:07
This question is not a duplicate of http://stackoverflow.com/questions/14394002 because I am asking about adding id to one table based on **sorted order**. The other question is technically about assigning an id to a view of union of tables. Sorting wasn't in the question though it's possible. The concerns are different. — thor, Jan 07 '15 at 17:32

score 3 · Accepted Answer · answered Dec 20 '14 at 18:18

3

You can use the row_number() window function:

SELECT   country.*, ROW_NUMBER() OVER (ORDER BY iso)
FROM     country
ORDER BY iso

answered Dec 20 '14 at 18:18

Mureinik

297,002
52
306
350

How to add an ordinal id field based on a sort order in PostgreSQL

1 Answers1