Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Enterprise
Expertise: Intermediate
Sep 10, 2001

Implementing Multilevel Ranking in T-SQL


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

Cid         peo
----------- -----------
1           20000331
1           20000630
1           20000930
1           20001231
2           20000331
2           20000630
2           20000930
2           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 NULL

drop table #fid

declare @Start int
declare @FID int
select @Start = 0

select distinct fid into #fid from tblRanking

update t1 set @Start = Rank = case when t1.fid = 
@FID then @Start + 1 else
1 end,@fid = t2.FID from tblRanking t1 ,#fid t2 
where t1.fid = t2.fid

select * from tblRanking

--///////////////////////////////////////

fid         peo         rank
----------- ----------- -----------
1           20000331    1
1           20000630    2
1           20000930    3
1           20001231    4
2           20000331    1
2           20000630    2
2           20000930    3
2           20001231    4
Harinatha Reddy
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date