CODEWITHSUNDEEP
sqlsql-serverranking
Ricol
Ricol

Reputation: 377

SQL ranking and ties

I'd like to rank some data

If I used a rank function with ties, the same ranking is assigned and a gap appears in the sequence for each duplicate ranking.

example :

Value | Ranking 
1       1  
1       1 
1       1
1       1 
1       1  
2       6

EDIT : I'd like to know if it's possible have these two versions :

Value | Ranking 
1       5    
1       5 
1       5  
1       5  
1       5   
2       6  

Value | Ranking 
1       3  
1       3 
1       3  
1       3 
1       3 
2       6

I replace 1 by 3 because 3 is the median value of 1-2-3-4-5 (5 ties values)

Upvotes: 1

Views: 2820

Answers (4)

user3556852
user3556852

Reputation: 49

You might want what I've been trying NOT to get.

ID # Rank
A   100 1
B   90  3
C   90  3
D   80  5
E   80  5

SELECT x.*, 
COUNT(*) AS myRank 
FROM myTablename x 
JOIN myTablename y 
ON x.number > y.number 
GROUP BY id 
ORDER BY myRank

Same results with ORDER BY x.number

Upvotes: 0

gbn
gbn

Reputation: 432411

DECLARE @foo TABLE (VALUE int);
INSERT @foo VALUES (1),(1),(1),(1),(1),(6);

WITH RowRank AS
(
    SELECT
        VALUE,  
        ROW_NUMBER() OVER (PARTITION BY VALUE ORDER BY VALUE) AS rn,
        RANK() OVER (ORDER BY VALUE) AS rk
    FROM
        @foo
), AvgMax AS
(
    SELECT
        VALUE, rn, rk,
        AVG(rn) OVER (PARTITION BY VALUE ORDER BY VALUE) AS av,
        MAX(rn) OVER (PARTITION BY VALUE ORDER BY VALUE) AS mx
    FROM 
        RowRank
)
SELECT
    VALUE,
    CASE WHEN av < rk THEN rk ELSE av END,
    CASE WHEN mx < rk THEN rk ELSE mx END
FROM
    AvgMax;

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44336

SELECT Value, 
count(*) over (partition by value)/2 + rank() over(order by value) as Ranking1, 
count(*) over (partition by value) + rank() over(order by value) -1 as Ranking2 
FROM table

Upvotes: 2

podiluska
podiluska

Reputation: 51504

Try

select 
    value, 
    RANK() over (order by value) 
             + COUNT(value) OVER (PARTITION BY value)  / 2,
    RANK() over (order by value) 
             + COUNT(value) OVER (PARTITION BY value) - 1
from yourtable t

If you're using SQL 2005, use

(select COUNT(*) from yourtable where value = t.value)

instead of the count over clause.

Upvotes: 1

Related Questions