How to generate sequential row number in tsql?

Question

I have a requirement in a report to show alternate colors in row and for this I need to generate sequential numbers in a SQL Select statement (see example below) to use later while displaying rows. I am trying row_number and some other techniques its not working. This should not be done using script, I should be able to generate within Select statement. Appreciate any help.

RowNumber - 1, Otherdata - Something1
RowNumber - 2, Otherdata - Something2
RowNumber - 3, Otherdata - Something3
RowNumber - 4, Otherdata - Something4
RowNumber - 5, Otherdata - Something5

Answer 1

There is no need to avoid Analytic Functions if your database supports them e.g ROW_NUMBER()

    SELECT
        ROW_NUMBER() OVER (ORDER BY [<PRIMARYKEY_COLUMN_NAME>]) AS Number
    FROM
        [<TABLE_NAME>]

The syntax is Func([ arguments ]) OVER (analytic_clause) you need to focus on OVER (). This last parentheses make partition(s) of your rows and apply the Func() on these partitions one by one. In above code we have only single set/partition of rows. Therefore the generated sequence is for all the rows.

You can make multiple set of your data and generate sequence number for each one in a single go. For example if you need generate sequence number for all the set of rows those have same categoryId. You just need to add Partition By clause like this (PARTITION BY categoryId ORDER BY [<PRIMARYKEY_COLUMN_NAME>]).

Remember that after FROM you can also use another extra ORDER BY to sort your data differently. But it has no effect on the OVER ()

Answer 2

If sort column contains unique values, you can also do it without the new built-in Row_Number() function, by using a subquery based on a sort column.

  Select [other stuff],
      (Select count(*) From table 
       where sortCol < a.sortCol) rowNum
  From table a
  Order by sortCol

change < to <= to start counting at 1 instead of 0