Ranking sqlserver records

Question

0.00/5 (No votes)

See more:

Hi there. I have the following database table

(sampleTable)

AverageMark Position
97
96 NULL
25 NULL
36 NULL
78.25 NULL
96 NULL

I Have Managed to write a C# Command that selects all

the records from sampleTable and arrange them in DESC

Order acccording to the AverageMark Column. I have

managed to update the position column according to

scores in the AverageMark. E.g 97 and 96 is position 2

and so on. How can i then consider the fact that there

are two figures (96) that follow in position 2.

Therefore because of this tie, i am sure position 3

should not exist. But instead postion 4. how can i

achieve this?

Posted 21-Jan-12 21:00pm

katumbamartin

Add a Solution

2 solutions

Solution 1

Hi,

just use one of ranking functions:
http://msdn.microsoft.com/en-us/library/ms189798.aspx[^]

Regards
Robert

Posted 22-Jan-12 0:54am

Kanasz Robert

Add a Solution

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

Costica U · Accepted Answer · 2012-01-22T01:56:00

Try this

SQL

if object_id('tempdb..#temp') is not null
    drop table #temp

create table #temp
(
AverageMark decimal(10,2),
Position    int
)


insert into #temp(AverageMark)values(97)
insert into #temp(AverageMark)values(96)
insert into #temp(AverageMark)values(25)
insert into #temp(AverageMark)values(36)
insert into #temp(AverageMark)values(78.25)
insert into #temp(AverageMark)values(96)

-- 1)  SQL2005 and up
select  AverageMark,
        rank() over(order by AverageMark desc) as Position
from    #temp
order by AverageMark desc

-- 2)  
select  AverageMark,
        (select count(*) from #temp where AverageMark > a.AverageMark) + 1  as Position
from    #temp a
order by AverageMark desc

Ranking sqlserver records

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Ranking sqlserver records

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us