SQL: Add column with incremental id to SELECT

Question

I have a simple query like:

SELECT name FROM people;

The people table does not a have unique id column. I want to add to the query result a column id with incremental int starting from 0 or 1 (it doesn't matter). How can one achieve this? (postgresql DB)

Answer 1

Use ROW_NUMBER():

SQLFiddle

SELECT 
  name,
  ROW_NUMBER() OVER (ORDER BY name) AS id
FROM people;

EDIT:

Difference between ORDER BY 1 vs ORDER BY column_name

SQLFiddleDemo

SELECT 
    name,
    ROW_NUMBER() OVER (ORDER BY name) AS id
FROM people;

/* Execution Plan */
QUERY PLAN WindowAgg (cost=83.37..104.37 rows=1200 width=38)
-> Sort (cost=83.37..86.37 rows=1200 width=38)
**Sort Key: name**
-> Seq Scan on people (cost=0.00..22.00 rows=1200 width=38)

SELECT 
    name,
    ROW_NUMBER() OVER (ORDER BY 1) AS id
FROM people;

/* Execution Plan */
QUERY PLAN WindowAgg (cost=0.00..37.00 rows=1200 width=38)
-> Seq Scan on people (cost=0.00..22.00 rows=1200 width=38)

In second case there is no sort operation.

You can also write second query as:

SELECT 
    name,
    ROW_NUMBER() OVER () AS id
FROM people;

Why people write ORDER BY 1 in windowed functions?

Because in some dialects it is required and ORDER BY 1 acts like placeholder.

Oracle:

ORA-30485: missing ORDER BY expression in the window specification

SELECT 
  name,
  ROW_NUMBER() OVER (ORDER BY 1) AS id
FROM people;

TSQL:

The function 'ROW_NUMBER' must have an OVER clause with ORDER BY.

SELECT 
    name,
    ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS id
FROM people;

Answer 2

The row_number window function should fit the bill:

SELECT ROW_NUMBER() OVER (ORDER BY 1), *
FROM   people