Add an autoincremental number to SQL query-CodePudding

Suppose I have a table like the following.

CREATE TABLE animal
(
    name VARCHAR (50), 
);

INSERT INTO animal VALUES ('Cat');
INSERT INTO animal VALUES ('Dog');
INSERT INTO animal VALUES ('Elephant');

Is there a way to place an autoincrement column in such a way that the result is as follows?

1,Cat
2,Dog
3,Elephant

CodePudding user response：

You can use Row_Number()

SELECT ROW_NUMBER() OVER (ORDER BY [name]) RowNum, [name]
FROM animal;

CodePudding user response：

You need row_number() :

select row_number() over(order by (select null)) as id, name
from animal;