Trying to calculate the RANK of a ticket from the Table, on MySQL 8 getting "You have an error in your SQL syntax", but is working fine on lower MySQL versions. Cannot figure it out, Thank you for the help:
SELECT z.rank FROM (
SELECT t.rb , t.tid, @rownum := @rownum + 1 AS rank
FROM br t, (SELECT @rownum := 0) r
ORDER BY rb ASC
) as z WHERE tid= '100100'
I'm pretty sure that the error you get is this
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rank ....
Well, that's cause rank is a reserved keyword since MySQL v8.0.2. Refer doc
What you can do with your current query is simply change rank to something else like t_rank and you should get the query working again.
SELECT z.t_rank FROM (
SELECT t.rb , t.tid, @rownum := @rownum + 1 AS t_rank
FROM br t, (SELECT @rownum := 0) r
ORDER BY rb ASC
) as z WHERE tid= '100100'
However, on MySQL v8+, you can directly use RANK() function without needing to use variables anymore like this:
SELECT t.rb , t.tid, RANK() OVER (ORDER BY rb) AS t_rank
FROM br t
ORDER BY rb;
That should return you the same (and possibly more accurate) results.
rank keyword by wrapping it in backticks (`) but I personally wouldn't because I work with some programming code that uses backticks a lot so it wouldn't be ideal for me to use it.
RANK()window function. Use that. dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html