# Use of SET command in find\_by\_sql

**URL:** <https://www.ruby-forum.com/t/use-of-set-command-in-find-by-sql/50828>\
**Category:** Rails\
**Created:** [December 21, 2005, 5:53pm UTC](https://www.ruby-forum.com/t/use-of-set-command-in-find-by-sql/50828 "2005-12-21T17:53:17Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![steveoco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/db5fbb/32.png) [@steveoco](https://www.ruby-forum.com/u/steveoco)\
**Post date:** [December 21, 2005, 5:53pm UTC](https://www.ruby-forum.com/t/use-of-set-command-in-find-by-sql/50828/1 "2005-12-21T17:53:17Z")

</div>

Hi,

I’m trying to execute something like this method in a model:

```
def self.sql_for_rankings()
    sql = "SET @counter:=0;"
    sql << " SET @counter:=0;
                SELECT *, @counter:=@counter+1 AS rank
                FROM testscores. "
    find_by_sql(sql)
end

```

Where the new rank column is a kind of autoincrementer inside of this  
query.  
Ignore for the moment that I can add this counter in ruby rather than in  
sql

- but I am trying to do it in mysql. This executes in mysql with no  
problem  
and returns something like:

|testscore; name, rank|  
|85, steve, 2|  
|92, tom, 1|

But when I try to run it in rails, it doesn’t like the SET command. It  
complains:

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 ';SELECT @counter:=@counter+1 AS rank, p.user\_id, p.user\_name,  
p.delta, p.profit  
’ at line 1: SET @counter:=0;SELECT \*, @counter:=@counter+1 AS rank  
FROM testscores

Can rails not handle this?

Thanks,

Steve  
[http://www.smarkets.net](http://www.smarkets.net)

---

<div class="post-metadata">

**Author:** ![steveoco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/db5fbb/32.png) [@steveoco](https://www.ruby-forum.com/u/steveoco)\
**Post date:** [December 21, 2005, 5:59pm UTC](https://www.ruby-forum.com/t/use-of-set-command-in-find-by-sql/50828/2 "2005-12-21T17:59:21Z")

</div>

> I’m trying to execute something like this method in a model:
> 
> ```
> def self.sql_for_rankings()
> sql = "SET @counter:=0;"
> sql << " SET @counter:=0;
> SELECT *, @counter:=@counter+1 AS rank
> FROM testscores. "
> find_by_sql(sql)
> end
> 
> ```

Why not just :

```
 def self.sql_for_rankings()
     sql = " SET @counter:=0;
                 SELECT *, @counter:=@counter+1 AS rank
                 FROM testscores. "
     find_by_sql(sql)
 end
```

---

<div class="post-metadata">

**Author:** ![steveoco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/db5fbb/32.png) [@steveoco](https://www.ruby-forum.com/u/steveoco)\
**Post date:** [December 21, 2005, 6:08pm UTC](https://www.ruby-forum.com/t/use-of-set-command-in-find-by-sql/50828/3 "2005-12-21T18:08:22Z")

</div>

Yep, I’ve tried that - same error message. I started with that same  
formating then moved to combining the strings thinking it was getting  
hung  
up on the ; after :=0 in the first line.

I’ve also tried to not use find\_by\_sql and instead tried something like:  
def self.sql\_for\_rankings()  
res = ActiveRecord::Base.connection.select\_one \<\<-EOL  
SET @counter:=0;  
SELECT \*, SELECT \*, @counter:=@counter+1 AS rank  
FROM testscores  
EOL  
end

But it always gets hung up on the SET line. (though it works if I run it  
directly in mysql.

Steve

---

<div class="post-metadata">

**Author:** ![steveoco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/db5fbb/32.png) [@steveoco](https://www.ruby-forum.com/u/steveoco)\
**Post date:** [December 21, 2005, 6:35pm UTC](https://www.ruby-forum.com/t/use-of-set-command-in-find-by-sql/50828/4 "2005-12-21T18:35:34Z")

</div>

i could be wrong, but i think the problem lies in the fact that you are  
trying to execute mulitple queries.

---

<div class="post-metadata">

**Author:** ![steveoco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/db5fbb/32.png) [@steveoco](https://www.ruby-forum.com/u/steveoco)\
**Post date:** [December 21, 2005, 7:32pm UTC](https://www.ruby-forum.com/t/use-of-set-command-in-find-by-sql/50828/5 "2005-12-21T19:32:47Z")

</div>

Oh sorry. Those were a cut and paste error. It should read:

def self.sql\_for\_rankings()  
res = ActiveRecord::Base.connection.select\_one \<\<-EOL  
SET @counter:=0;  
SELECT \*, @counter:=@counter+1 AS rank  
FROM testscores  
EOL  
end

And my original should read:

def self.sql\_for\_rankings()  
sql = “SET @counter:=0;”  
sql \<\< "SELECT \*, @counter:=@counter+1 AS rank  
FROM testscores. "  
find\_by\_sql(sql)  
end

Thanks.There should only be one select and one set. But it still returns  
an  
error. And like I said, it works in mysql directly but not in rails.

Steve

---

<div class="post-metadata">

**Author:** ![steveoco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/db5fbb/32.png) [@steveoco](https://www.ruby-forum.com/u/steveoco)\
**Post date:** [December 21, 2005, 7:47pm UTC](https://www.ruby-forum.com/t/use-of-set-command-in-find-by-sql/50828/6 "2005-12-21T19:47:59Z")

</div>

* * *

Rails mailing list  
removed\_email\_address@domain.invalid  
[http://lists.rubyonrails.org/mailman/listinfo/rails](http://lists.rubyonrails.org/mailman/listinfo/rails)

---

<div class="post-metadata">

**Author:** ![steveoco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/db5fbb/32.png) [@steveoco](https://www.ruby-forum.com/u/steveoco)\
**Post date:** [December 21, 2005, 7:51pm UTC](https://www.ruby-forum.com/t/use-of-set-command-in-find-by-sql/50828/7 "2005-12-21T19:51:03Z")

</div>

as I said, i don’t think you can chain queries together like that in  
rails.

try something like

class testscore \< ActiveRecord::Base  
def self.sql\_for\_rankings  
begin  
connection.execute(“SET @counter:=0”)  
find\_by\_sql(“select \*, @counter:=@counter+1 AS rank FROM  
testscores”)  
rescue NotImplementedError  
# execute not implemented  
end  
end  
end

---

<div class="post-metadata">

**Author:** ![steveoco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/db5fbb/32.png) [@steveoco](https://www.ruby-forum.com/u/steveoco)\
**Post date:** [December 21, 2005, 8:30pm UTC](https://www.ruby-forum.com/t/use-of-set-command-in-find-by-sql/50828/8 "2005-12-21T20:30:26Z")

</div>

That worked, Chris.

I was not associating a SET statement as a query, but I guess that is  
what  
it is.

Thanks.

Steve