Rank books with ties

Programming in sql: Rank books with ties on newest questions tagged sql – Stack Overflow

I have the following query so far, which works perfectly to rank the books based on our internal score.

UPDATE CER
SET CER.book_rank = Ranker.ranc
FROM book_ranks CER
INNER JOIN
(SELECT Rank() over (Order by book_score desc, book_id) as ranc, book_id
FROM book_ranks
WHERE Category = 'Fiction'
GROUP BY book_id, book_score
) Ranker
ON
CER.book_id = Ranker.book_id

The code is working perfect, but not taking care of case.

Input:

bookName  book_score
--------  ----------
book2     45
book3     35
book5     35
book7     35
book9     30

Current Output:

bookName  book_score  book_rank
--------  ----------  ---------
book2     45          1
book3     35          2
book5     35          3
book7     35          4
book9     30          5

Required Output:

bookName  book_score  book_rank
--------  ----------  ---------
book2     45          1
book3     35          2
book5     35          2
book7     35          2
book9     30          5

As book3, book5, book7 has the same score, their rank should be same and book9 should have rank of 5.

source: http://stackoverflow.com/questions/11148137/rank-books-with-ties
Programming in sql: programming-in-sql