Using MySQL user-defined variables with database class

[eluser]nsbingham[/eluser]
I'm attempting to dynamically create a rank # based on a sort of a column in a table using something like the following:

Code:

SET @rank:0;SELECT *, @rank := @rank + 1 FROM table_name;

CI is throwing a "You have an error in your SQL syntax" whenever I attempt to include the "SET @rank:0" statement in the query.

Code:

$query = $this->db->query("SET @rank:0;SELECT *, @rank := @rank + 1 FROM table_name;");

Has anyone else had success with this or is it even possible to use MySQL user-defined variables with CI's Database Class or ActiveRecord?

I'm needing to do some sorting based on the rank and so that's why I'm trying to avoid creating another column and storing the rank.

Thanks,
Nathan