Ranking of results - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Ranking of results

Author

Topic

MADTIES
Starting Member

6 Posts

Posted - 2006-03-04 : 15:12:34

There may already be an answer for this but I wasn't sure what to search for :O)

What I need is to be able to add a ranking number against a set of results based on the Order By values. However rather than having a numeric 'position' against all the results, I need to reset it based on how the data is grouped. An example...

Date Name Rank
1/1/06 Jeff 1
1/1/06 Chris 2
1/1/06 John 3
3/1/06 Michelle 1
3/1/06 Peter 2
3/1/06 Mary 3

Any suggestions would be appreciated.

Regards

MADTIES
Starting Member

6 Posts

Posted - 2006-03-04 : 15:36:43

Extra info: I'm using SQL 2000 but something that operated like ROW_NUMBER() in SQL 2005 would be perfect.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-04 : 15:43:15

Not sure how you are ranking within each group but here is an example of updating the table and ranking on the fly:


set nocount on
create table #junk (dt datetime, nm varchar(10), rank int null)
go
insert #junk (dt, nm)
select '1/1/06', 'Jeff' union 
select '1/1/06', 'Chris' union 
select '1/1/06', 'John' union 
select '3/1/06', 'Michelle' union 
select '3/1/06', 'Peter' union 
select '3/1/06', 'Mary'
go
select * from #junk

print '
Update the table with rank value by group'
update a set
	rank = (select count(*) 
		from #junk 
		where datediff(day,dt,a.dt) = 0
		and nm <= a.nm)
from	#junk a

select * from #junk

print '
Select out the values calculating rank on the fly'
select	dt
	,nm
	,rank = (select count(*) 
		from #junk 
		where datediff(day,dt,a.dt) = 0
		and nm <= a.nm)
from	#junk a
go
drop table #junk

output:
Update the table with rank value by group
dt                                                     nm         rank        
------------------------------------------------------ ---------- ----------- 
2006-01-01 00:00:00.000                                Chris      1
2006-01-01 00:00:00.000                                Jeff       2
2006-01-01 00:00:00.000                                John       3
2006-03-01 00:00:00.000                                Mary       1
2006-03-01 00:00:00.000                                Michelle   2
2006-03-01 00:00:00.000                                Peter      3


Select out the values calculating rank on the fly
dt                                                     nm         rank        
------------------------------------------------------ ---------- ----------- 
2006-01-01 00:00:00.000                                Chris      1
2006-01-01 00:00:00.000                                Jeff       2
2006-01-01 00:00:00.000                                John       3
2006-03-01 00:00:00.000                                Mary       1
2006-03-01 00:00:00.000                                Michelle   2
2006-03-01 00:00:00.000                                Peter      3

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-06 : 01:34:19

Where do you want to show the data?
If you use Reports, Group it by Date, then use RecordNumber feature and reset in every group. Doing this in sql will be inefficient if your table has millions of rows

Madhivanan

Failing to plan is Planning to fail

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-03-06 : 01:38:47

go thru this link as well
http://sqlteam.com/forums/topic.asp?TOPIC_ID=56448

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources