Session Variables in a DB Query

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.


Try breaking that up into two SQLExecute’s and then the SQLSelect on the same connection and see what happens.

Thanks very much Kem. That worked very well when I hard-coded the “Set” statements into sqlexecute commands. Now I have to figure out how to integrate that step flexibly/efficiently into my larger database query program which allows me to enter any sql I want for a variety of databases and tables I maintain, but which until this instance, I’ve never had to use @Set statements before.

By the way, since I hadn’t mentioned it and you wondered, I am using MySQL as the database.

Thanks again.