Vb.net Student Ranking

Question

I need some assistance, I'm working on a school project and having issue ranking the students according to the school specification. For example, in a class of 10 pupils, each pupil's score is 80,70,70,60,60,50,40,30,30,20 respectively. When using Rank in SQL Server to get each pupil's rank, it will output this

Score   Rank
------------
80      1
70      2
70      3
60      4
60      5
50      6
40      7
30      8
30      9
20      10

What I want is for pupils with the same score to have the same ranking. It should look like this

Score   Rank
------------
80      1
70      2
70      2        
60      4
60      5
50      6
30      7
30      7
30      7
20      10

I am using vb.net 2013 and Sql server 2012, and I'm displaying the data from the database to a DataGridView.

Where are you running into issues so far? Can you show us your current code / script? — Adam V, Feb 16 '16 at 22:46
SQL is the generic query language for most all DBs. which db specifically are you using ( some have this built in). Your question could merit from some line breaks, punctuation...and code showing what you tried. — Ňɏssa Pøngjǣrdenlarp, Feb 16 '16 at 23:01
"SELECT MarkEntry.[Total Score] AS Total,ROW_NUMBER() OVER (ORDER BY sum(markentry.TotalScore) Desc) AS Position, FROM MarkEntry, All the data is coming from database to datagridview and the result was table 1 above and i want it to be like table 2 — Samuel, Feb 16 '16 at 23:37
Possible duplicate of [How to use RANK() in SQL Server](http://stackoverflow.com/questions/12739208/how-to-use-rank-in-sql-server) — Ňɏssa Pøngjǣrdenlarp, Feb 17 '16 at 00:07
I cant solve it using rank in sql server, pls try solve for me — Samuel, Feb 17 '16 at 00:25

score 0 · Accepted Answer · edited Feb 18 '16 at 08:09

DECLARE @Table TABLE(
    ContenderNum INT,
    totals FLOAT
)

INSERT INTO @Table SELECT 1, 196
INSERT INTO @Table SELECT 2, 181
INSERT INTO @Table SELECT 3, 192
INSERT INTO @Table SELECT 4, 181
INSERT INTO @Table SELECT 5, 179

SELECT contendernum,totals, 
    RANK() OVER (ORDER BY totals DESC) AS xRank,
    DENSE_RANK() OVER (ORDER BY totals DESC) AS xRank
FROM 
( 
    SELECT ContenderNum ,totals 
    FROM @Table
) AS a
ORDER BY 1

It solve it, I was using SQL Row_Number.

Vb.net Student Ranking

1 Answers1