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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Looking for help with ranking, ratings
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

21 Posts

Posted - 09/27/2013 :  12:41:04  Show Profile  Reply with Quote
Hello, I am looking for some help with my personal NFL football database. I would like to Rate each team with small integer value based on their stats.

team char(20),
offense decimal (4,1)
defense decimal (4,1)

What I would like to do is rate each team based on their stats.

Here are the rating conditions.

rank teams in descending order based on offense.
top 5 teams award them a rating of 3
next 5 rating of 2
next 5 rating of 1
next 5 rating of 0
next 5 rating of -1
next 5 rating of -2
last 2 rating of -3

There are exactly 32 teams.

I would also like to do the same rating for defense but the sort order for ranking should be ascending.

And I would like store the sum of each individual stat group as well as the sum of them combined for a total rating of each team.

I am not looking for the work to be done for me, but I am asking for help in pointing me in the right direction of methodology and maybe some key commands for me to research.

thank you

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 09/27/2013 :  12:51:53  Show Profile  Reply with Quote
For offense, you can do it like shown below. For defense, add another rank column in the inner query and similar case expression in the outer query.
		WHEN OffenseRank <= 5 THEN 3
		WHEN OffenseRank <= 10 THEN 2
		WHEN OffenseRank <= 15 THEN 1
		WHEN OffenseRank <= 20 THEN 0
		WHEN OffenseRank <= 25 THEN -1
		WHEN OffenseRank <= 30 THEN -2
		ELSE -3
	END AS OffensePoints
   SELECT *, RANK() OVER (ORDER BY offense DESC) AS OffenseRank 
   FROM tbl_team_stats
) s
Go to Top of Page

Starting Member

21 Posts

Posted - 09/27/2013 :  14:43:14  Show Profile  Reply with Quote
thanks a lot, that worked perfect. I forgot to tell you about the season column, and how I wanted an aggregrate rating over multiple seasons and individual seasons.....but I figured that out.

I am starting to understand the rank function and have plenty of uses for it
Go to Top of Page

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 09/28/2013 :  03:22:16  Show Profile  Reply with Quote
what if you've more than 35 records. the below suggestion will work for any number of records continuing ranking as per your sequence

DECLARE @StartValue int
SET @StartValue=3
	@StartValue + (1-OffenseRank)  AS OffensePoints
   SELECT *, RANK() OVER (ORDER BY offense DESC) AS OffenseRank 
   FROM tbl_team_stats
) s

Set any value of startvalue and it will continue from it as per your sequence

SQL Server MVP
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000