devxlogo

Implementing Multilevel Ranking in T-SQL

Implementing Multilevel Ranking in T-SQL

You might have a table with different PEOs for each Company ID. For example:

 Table name : tblRankingCid         peo----------- -----------1           200003311           200006301           200009301           200012312           200003312           200006302           200009302           20001231

size=3>
To give PEO ranking for each CompanyId, add one column, say “Rank” to the present table and use the following script:

 --///////////////////////////////////////ALTER TABLE tblRanking ADD Rank int NULLdrop table #fiddeclare @Start intdeclare @FID intselect @Start = 0select distinct fid into #fid from tblRankingupdate t1 set @Start = Rank = case when t1.fid = @FID then @Start + 1 else1 end,@fid = t2.FID from tblRanking t1 ,#fid t2 where t1.fid = t2.fidselect * from tblRanking--///////////////////////////////////////fid         peo         rank----------- ----------- -----------1           20000331    11           20000630    21           20000930    31           20001231    42           20000331    12           20000630    22           20000930    32           20001231    4

size=3>

devx-admin

Share the Post: