[SOLVED] Need to change a MYSQL query using the @ as we updated our Mysql version

Need to change a MYSQL query using the @ as we updated our Mysql version

We updated our Mysql Driver to

Database Name MySQL Database Version 5.6.10-log Driver Name MySQL-AB JDBC Driver

We were using an older version but no one knows what that version was as that machine is DEAD. The query below is running within our railo web site. The MySQL server does not like the @ but I have no idea how to re-write as MySQL is not my thing and this was code written many many moons ago.

set @row = 0;

select nf.nid, @row:=@row+1 as ranking from financial nf 
where nf.year = (select distinct year from financial where type = 'Total income' Order by year DESC LIMIT 1) 
and nf.type in ('Total Spend','Total budget (Spend)') 
and nf.nid in (select ft_no from n where ft_type in (1)) 
and nf.value > 0 
order by nf.value desc

If anyone knows Mysql better than me (most people) please help me solve this issue. I am sure you will find more issues with the query so any help is welcomed.

Thanks in advance Andrea

Solution

Sometimes there is a problem running multiple queries in a single query. If that is the issue, you can solve this by setting the initial value in the query:

select nf.nid, (@row := @row+1) as ranking
from financial nf cross join
     (select @row := 0) params
where nf.year = (select max(year)
                 from financial
                 where type = 'Total income'
                ) and
     nf.type in ('Total Spend', 'Total budget (Spend)') and
     nf.nid in (select ft_no from n where ft_type in (1)) and
     nf.value > 0 
order by nf.value desc;

I simplified the subquery for year as well.