Get dynamic row pointer

Question

I created an SQL query that arranges all the rows of a table in a specific way.

Q: How can I get the line number (index?) Of a specific line in such a dynamically generated list?

Thanks in advance.

Answer 1

SELECT Row_NUMBER() OVER (ORDER BY <your criteria>) as RowIndex, other fields FROM MyTable 

Update

To add this as a field, you can:

 UPDATE T SET T.Indexfield = X.RowIndex FROM MyTable T INNER JOIN (SELECT Row_NUMBER() OVER (ORDER BY <your criteria>) as RowIndex, other fields FROM MyTable) as X ON X.<field> = T.<Field>