SQL row_number() error

my code:

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.

What error?

maybe from the error message we can also figure out which dbms it is…

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

[quote=“Beginner, post:4, topic:191552, full:true”]
I just found out that row_number() does not exist in mysql, [/quote]
that was my suspicion

what you have is fine, if a bit slower than just a simple query

you can also do the ranking in your application language (php or whatever)

Thank you.
Have a nice day!!^^

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.