I am trying to rank the top receivers for each NFL team and then list each team’s top three using an sql query that relies on session variables. The following query works in phpmyadmin:
set @rank = 1;
set @theteam = "";
SELECT full_team, name, rec, rank
FROM
(SELECT full_team, name, rec,
@rank := IF(@theteam = full_team, @rank + 1, 1) AS rank,
@theteam := full_team
FROM pfr_receiving
where year = 2018
ORDER BY full_team, rec DESC
) ranked
WHERE rank <= 3;
which returns
full_team name rec rank
Arizona Cardinals Larry Fitzgerald 69 1
Arizona Cardinals David Johnson 50 2
Arizona Cardinals Christian Kirk 43 3
Atlanta Falcons Julio Jones 113 1
Atlanta Falcons Austin Hooper 71 2
Atlanta Falcons Mohamed Sanu 66 3
Baltimore Ravens Willie Snead 62 1
...
When I enter that sql into an XOJO sqlselect statement, however, I get zero results unless I remove the top two lines (set @rank… and set @theteam…) in which case I get every player for each team and they each have a rank of 1.
So, is there anyway to use session variables in XOJO to create ranks like I can in phpmyadmin.