Add Index to postgreSQL query result

Question

My query result looks like this:

| A | B |
|-------|
| 1 | 2 |
| 1 | 4 |
| 1 | 6 |
| 1 | 9 |
| 1 | 1 |
| 1 | 6 |
| 1 | 9 |

Now I want to increase column A by the index of the result table, so the result would become like this:

| A | B |
|-------|
| 2 | 2 |
| 3 | 4 |
| 4 | 6 |
| 5 | 9 |
| 6 | 1 |
| 7 | 6 |
| 8 | 9 |

How can I do it? Thanks!

Why do you want to start from 2? Do you always want to start from 2 or because b starts from 2? — MatBailie
– MatBailie, Commented Mar 20, 2019 at 13:34

Yogesh Sharma · Accepted Answer · 2019-03-20 13:31:50Z

2

You want row_number()

select (row_number() over (order by a) + 1) as A, b
from table t;

answered Mar 20, 2019 at 13:31

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

There is no need to put an expression between parentheses

Thiago Virgilio · Accepted Answer · 2019-03-20 13:36:50Z

0

Maybe something like that:

SELECT 
    (row_number() OVER (ORDER BY A) + A) AS columnAIndex, 
     columnB
FROM ...

I don't have a PostgreSQL client installed here, therefore, i don't tested this query.

answered Mar 20, 2019 at 13:36

Thiago Virgilio

11 bronze badge