Get Ranking using ROW_Number() without specific ordering in SQL Server

As we all knows that, to get ranking with query we use ROW_Number() in sql server but this issue with Row_Number() is that it only works with "order by" and sometimes we face a situation where we wants the ranking without doing any change in its ordering. To do so, you can do a trick to provide order by to Row_Number as well as it will not affect ordering of your result.

With ordering, this will provide result with row_number but in asc ordering by field1:

 select field1, field2,ROW_NUMBER () OVER (ORDER BY field1) AS RowNum from TableName

Without ordering, this will provide result in order as of query with row number:

 select field1, field2,ROW_NUMBER () OVER (ORDER BY (SELECT 1)) AS RowNum from TableName

so we have used "(select 1)" instead of specifying any field name for ordering so it will not affect order of query result

Compiled By: Rajesh Rolen