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


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

See also  Comparing different methods of testing your Infrastructure-as-Code
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist