SQL 2000 - RANKING

• ShuaibV

  SSCrazy

  Points: 2001

  More actions

  March 30, 2009 at 2:31 pm

  #73596

  Hi

  Please help to write a sql query to arrive the below mentioned output:

  Input Table

  Col1 BatchNo

  1001 1

  1002 1

  1002 1

  1001 2

  1001 2

  1002 2

  1003 2

  Output needed

  ID Col1

  1 1001

  2 1002

  2 1002

  3 1001

  3 1001

  4 1002

  5 1003

  Thanks in Advance

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  March 30, 2009 at 2:47 pm

  #968691

  Does the following code help you?

  -- Create a test table for the sample code

  create table dbo.TestTable (

  ColID int,

  BatchNo int,

  RankID int null

  );

  --create a clustered index on the test table

  create clustered index IX_TestTable on dbo.TestTable(

  BatchNo asc,

  ColID asc

  );

  -- insert some test data

  insert into dbo.TestTable (ColID, BatchNo)

  select 1001, 1 union all

  select 1002, 1 union all

  select 1002, 1 union all

  select 1001, 2 union all

  select 1001, 2 union all

  select 1002, 2 union all

  select 1003, 2

  ;

  --show what'is in the test table

  select * from dbo.TestTable;

  -- now let's update the RankID

  declare @Rank int,

  @ColID int,

  @BatchNo int;

  set @Rank = 0;

  set @ColID = 0;

  set @BatchNo = 0;

  update dbo.TestTable set

  @Rank = RankID = @Rank + case when ColID = @ColID and BatchNo = @BatchNo then 0 else 1 end,

  @ColID = ColID,

  @BatchNo = BatchNo

  from

  dbo.TestTable with (Index = 1, TABLOCKX);

  --show what's now in the table

  select * from dbo.TestTable;

  -- drop the test table

  drop table dbo.TestTable;

• ShuaibV

  SSCrazy

  Points: 2001

  More actions

  March 30, 2009 at 3:23 pm

  #968728

  Thanks. It helped me to arrive the expected results.

  Can you please clarify the order of the data in temp table? I am inserting the data into temp table using dynamic SQL (one dynamic sql statement for each batch). Will the data be ordered according to Col1 & BatchNo?

  Thanks

  Shuaib

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  March 30, 2009 at 3:27 pm

  #968738

  Look carefully at the clustered index. That is what makes this work. Based on your sample data and expected results it is actualy based on BatchNo, ColID.

  Edit:

  Unfortunately, the article that would explain it better is currently unavailable as it is being rewritten.

• ShuaibV

  SSCrazy

  Points: 2001

  More actions

  March 30, 2009 at 3:33 pm

  #968743

  The local temporay table doesn't have the cluster index. I will create and hope it will not have any issues. Thanks for your help and info.