SELECT ROW_NUMBER() OVER (ORDER BY score DESC) AS rank,*
FROM book
WHERE bookgroup = 2
The practice is to rank all the books with the scores DESC given by readers. However, I got an error.
Is there any syntax error in my code? The data should be ok, I think.
I just found out that row_number() does not exist in mysql, and I also found the code which can do the rank part. However,as regards efficiency, I’m not sure if it’s the best way to do it.
SELECT b.id,
b.name,
b.score,
b.bookgroup,
(SELECT COUNT(1)+1
FROM book as a
WHERE a.score>b.score AND a.bookgroup = b.bookgroup
) as rank
FROM book as b
WHERE bookgroup = 2
ORDER by rank ASC