sql server - SQLServer SQL query with a row counter -

I have an SQL query that gives a set of rows:

  SELECT Id, name by user where group = 2  

I must also include a column with incremental integer value, so the first row should have 1 in the counter column, the second 2, the third one 3 etc.

The question shown here is just a simple example, in fact the questionnaire can be very complicated, many join And with nested queries.

I know this can be achieved using a floating table with an autonumber field, but is there a way to do this within the query?

For starters, something along the lines of:

  my_first_kelly , My_second_claim, ROW_NUMBER () (my_order_column by ORDER) my_table from AR Row_Counter  

However, it is important to keep in mind that ROW_NUMBER () over (ORDER BY ...) < / Code> builds onl Y Row_Counter , it does not guarantee the sequence of results.

Unless there is a clear code in SELECT section, the results can be returned in any order, depending on whether SQL How the server decides to optimize the query. (.)

The only way to ensure that the results will be returned in always Row_Counter , both select and < Code> ROW_NUMBER () :

  my_first_quelum, my_second_column, at row_NUMBER () (my_order_column by ORDER) my_table ORDER BY Row_Counter AS My_order_column - Row_Counter  

< The exact copy of the order to be used for p> will always return the results in the correct order and work well for simple questions, but an "arbitrary complex" query About re probably ORDER BY dozens of expression in the segment? In those circumstances, I like something instead:

  SELECT t * Select (my_first_column, my_second_column, ROW_NUMBER ()) from (ORDER BY ...) AS ROW_Counter - complex order from my_table) T. Using rouquent  

Nested queries mean there is no need to duplicate complex ORDER BY clauses, which means less dislocation and easy maintenance. External by command t.Row_Counter also intends to query your affiliate developers.