Add auto-incrementing column in temporary Common Table Expression

Question

I would like to add an auto-incrementing column to a table using a Common Table Expression.

Suppose we have a table TEMP with one column COL as follow:

TABLE TEMP

+---+
|COL|
+---+
|a  |
+---+
|b  |
+---+
|c  |
+---+
|d  |
+---+
|e  |
+---+
|f  |
+---+
|g  |
+---+
|h  |
+---+

Is it possible to add an auto-incrementing column to the temporary table and to have the following result?

Table CTE

+---+--+
|COL|ID|
+---+--+
|a  |1 |
+---+--+
|b  |2 |
+---+--+
|c  |3 |
+---+--+
|d  |4 |
+---+--+
|e  |5 |
+---+--+
|f  |6 |
+---+--+
|g  |7 |
+---+--+
|h  |8 |
+---+--+

Is It possible to make that using CTE?

Answer 1

You just need an analytic query, for instance row_number(), which returns the sequence of a row, within a partition (not required), and in the order specified.

select col, row_number() over ( order by col ) as id
  from temp