Add points to ranking in SQL server

Question

0.00/5 (No votes)

See more:

I would like to add certain points to rankings of competitors in a database for example

Avg              Placing/rank     points
  95              1st             140
  90             2nd              130
  85             3rd              120
  83             4th              110
  76             5th              100
  75             6th               90
  75             6th               90
  73             8th               80
  72             9th               70
  71             10th              60

if there is a tie for any of the placing then the respective competitors will recieve the same points for the placing and placing skips to the next as in placing 8th in the example above.

Please assist

What I have tried:

rank, row ranks,ranking function, how to rank in a database

Posted 8-Jan-18 11:45am

Member 13392936

Updated 8-Jan-18 22:21pm

PIEBALDconsult

v2

Add a Solution

Comments

Karthik_Mahalingam 8-Jan-18 23:44pm

what is your input and expected output?

Member 13392936 9-Jan-18 0:18am

my input is the average while which will determine the placing and the points gained as the output

Karthik_Mahalingam 9-Jan-18 0:23am

so you want the output in the above table format?

Member 13392936 9-Jan-18 1:46am

thanks much will try and provide feedback. greatly appreciated

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2018-01-08T22:21:00

Take a look at: Ranking Functions (Transact-SQL) | Microsoft Docs[^]. You need to use RANK function, for example:

SQL

SELECT Src.points, RANK() OVER(ORDER BY Src.Points DESC) AS Placing
FROM (
	SELECT 140 AS points UNION ALL
	SELECT 130 AS points UNION ALL
	SELECT 120 AS points UNION ALL
	SELECT 110 AS points UNION ALL
	SELECT 100 AS points UNION ALL
	SELECT 90 AS points UNION ALL
	SELECT 90 AS points UNION ALL
	SELECT 80 AS points UNION ALL
	SELECT 70 AS points UNION ALL
	SELECT 60 AS points
) AS Src

Result:

points	Placing
140	1
130	2
120	3
110	4
100	5
90	6
90	6
80	8
70	9
60	10

Pradeep Arthanari · Accepted Answer · 2018-01-08T19:20:00

you may try this:

Have hardcoded points in a seperate table for each rank. And created a temp table to find the rank first. Then inner join temp table with points table to map point based on rank.

declare  @avgrank table(avg1 int ,rank1 int  )
 insert into  @avgrank( avg1) values (95)
 insert into  @avgrank( avg1) values (90)
 insert into  @avgrank( avg1) values (85)
 insert into  @avgrank( avg1) values (83)
 insert into  @avgrank( avg1) values (76)
 insert into  @avgrank( avg1) values (75)
 insert into  @avgrank( avg1) values (75)
 insert into  @avgrank( avg1) values (73)
 insert into  @avgrank( avg1) values (72)
 insert into  @avgrank( avg1) values (71)


 declare @points table(rank1 int ,point int )
 insert into  @points values (1,140)
 insert into  @points values (2,130)
 insert into  @points values (3,120)
 insert into  @points values (4,110)
 insert into  @points values (5,100)
 insert into  @points values (6,90)
 insert into  @points values (7,80)
 insert into  @points values (8,70)
 insert into  @points values (9,60)
 


 IF OBJECT_ID('tempdb..#tempavg') IS NOT NULL
    DROP TABLE #tempavg

 SELECT avg1, RANK() OVER (ORDER BY avg1 DESC) AS Placing  
  INTO #tempavg FROM @avgrank a 

SELECT avg1,Placing, p.point FROM #tempavg ta INNER JOIN @points p ON ta.Placing = p.rank1

Add points to ranking in SQL server

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0