MS  SQL Server programmers and even administrators (making reports for managers from production database) quite often face the problem, that resulting record set must be numbered by some rule. For instance, top management wants the report of the best sold products grouped by category and ordered separetly within a particular category. Which would result in something like

  Before advent of SQL Server 2005 it used to be a real headache to add this bugging N column to an existing query and thus record set.

  Suppose that we already wrote the query that returns necessary information and the only problem is to add the N column. Lets create the table and fill it in with the neccessary information.

create table salesresults (

    Category varchar(100)

  , ProductName  varchar(100)

  , TotalSalesMoney money

  , TotalSalesQuantity int

)

insert into salesresults (  Category , ProductName , TotalSalesMoney , TotalSalesQuantity )

select'Food'  ,  'Butter'  ,11000,  678

union all select'Food'  ,  'Juice'  ,10000,  788

union all select'Food'  ,  'Meat'  ,4000,  75

union all select'Materials'  ,  'Rubber'  ,14000,  567

union all select'Materials'  ,  'Steel'  ,9000,  234

union all select'Materials'  ,  'Oil'  ,5000,  455

select * from salesresults

So now we have the query that returns report, but how to add the number of product in category ?

If you use sql server 2000 you can use  a temporary table or table variable and update it with Number in cursor.

declare @a table (

   ID int identity (1,1)  primary key clustered

   ,Category varchar(100)

  , N int

  , ProductName  varchar(100)

  , TotalSalesMoney money

  , TotalSalesQuantity int

  )

  insert into @a (  Category , ProductName , TotalSalesMoney , TotalSalesQuantity )

  select   Category , ProductName , TotalSalesMoney , TotalSalesQuantity

  from salesresults

 

declare c cursor for select id, Category from @a order by Category, TotalSalesMoney desc

open c

declare @cat varchar(100)

declare @prev varchar(100)

declare @current int

declare @id int

set @prev=''

set @current = 1

fetch next from c into @id , @cat

while @@FETCH_STATUS = 0

begin

if @cat <> @prev

  set @current = 1

else

  set @current = @current + 1

set @prev = @cat

update @a set N = @current

where ID = @id

fetch next from c into @id , @cat

end

 

select * from @a

order by Category, N

close c

deallocate c

  Quite not simple script , is not it? You may also avoid using cursors.

create  table #a (

   ID int identity (1,1)

   ,Category varchar(100)

  , N int

  , ProductName  varchar(100)

  , TotalSalesMoney money

  , TotalSalesQuantity int

  )

  create clustered  index aid on #a (category, TotalSalesMoney desc )

  insert into #a (  Category , ProductName , TotalSalesMoney , TotalSalesQuantity )

  select   Category , ProductName , TotalSalesMoney , TotalSalesQuantity

  from salesresults

 

declare @prev varchar(100)

declare @current int

set @prev=''

set @current = 1

update a set

  @current =N= case when @prev <> Category then 1 else @current + 1 end

  ,@prev = Category

 from #a a

 

 

select * from #a

order by Category, N

drop table #a

  Here you need to have necesssary index (aid) on temporary table to have the rows ordered properly and thus numerated in right order.

 In MS SQL Server 2005 and later on you can use new operator ROW_NUMBER which does all the work described above for you.

Syntax of this operator is as following

ROW_NUMBER( )     OVER ( [ <partition_by_clause> ] <order_by_clause> )

 Arguments

<partition_by_clause>

Divides the result set produced by the FROM clause into partitions to which the ROW_NUMBER function is applied. For the PARTITION BY syntax, see OVER Clause (Transact-SQL).

<order_by_clause>

Determines the order in which the ROW_NUMBER value is assigned to the rows in a partition. For more information, see ORDER BY Clause (Transact-SQL). An integer cannot represent a column when the <order_by_clause> is used in a ranking function.

 So in over case we can use it as

select Category

 , N = ROW_NUMBER() OVER(partition by Category order by totalsalesmoney desc)

 , ProductName

  , TotalSalesMoney

  , TotalSalesQuantity

from salesresults

New operator ROW_NUMBER()  alows you to number resulting sets in any order you wish using only one line of sql code per row. Let say we need to number categories as well in our report.