SQL order by and insert to each row position number

Is it possible to have select query with order by and as a result insert new 开发者_JAVA百科column with position of each row ?

for example I have table:

Name
-------
A
D
D
B
C
B

and as a result I would like to have table:

Name Position
-------------
A 1
D 4
D 4
B 2
B 2
C 3

thanks for any help

Use DENSE_RANK

SELECT Name ,
    DENSE_RANK() OVER (ORDER BY Name) AS 'Position'
    FROM Table

This will produce

Name Position
-------------
A   1
B   2
B   2
C   3
D   4
D   4

so it may be difficult to maintain the original ordering

use row_number

SELECT name,ROW_NUMBER() OVER(ORDER BY name) as Position
 FROM YourTable

SELECT 
    Name, 
    ROW_NUMBER() OVER(ORDER BY Name) AS POSITION 
FROM Table_1

SELECT Name, DENSE_RANK() OVER (ORDER BY Name) AS 'Position' 
FROM Table