SQL: add column with incremental identifier in SELECT

Question

I have a simple query like:

SELECT name FROM people;

Table

peopledoes not have a unique identifier column. I want to add an idincremental query to the search result column intstarting from 0/1 does not matter. How to achieve this? (postgresql DB)

Answer 1

Use ROW_NUMBER():

SQLFiddle

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

EDIT:

The difference between ORDER BY 1vsORDER 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 the second case, the sort operation is not performed.

You can also write the second query as:

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

Why do people write ORDER BY 1in window functions?

, ORDER BY 1 placeholder.

Oracle:

ORA-30485: ORDER BY

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

TSQL:

"ROW_NUMBER" OVER ORDER BY.

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

Answer 2

, row_number.

select row_number() over(order by name) as id, name
from people

Answer 3

row_number :

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

Answer 4

row_number() https://www.postgresql.org/docs/current/static/functions-window.html