# Converting the following sql

**URL:** <https://www.ruby-forum.com/t/converting-the-following-sql/149314>\
**Category:** Rails\
**Created:** [September 23, 2008, 2:31pm UTC](https://www.ruby-forum.com/t/converting-the-following-sql/149314 "2008-09-23T14:31:42Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![bskaggs](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ab097/32.png) [@bskaggs](https://www.ruby-forum.com/u/bskaggs)\
**Post date:** [September 23, 2008, 2:31pm UTC](https://www.ruby-forum.com/t/converting-the-following-sql/149314/1 "2008-09-23T14:31:42Z")

</div>

i have a query, i need to transfer this using all the find conditions  
etc, can anyone give me a few pointers?

select \* from outcomes o1 where o1.outcome\_date = (select  
max(o2.outcome\_date) from outcomes o2 where o2.testrun\_id =  
o1.testrun\_id and o2.testcase\_id = o1.testcase\_id);

Outcome.count ??

---

<div class="post-metadata">

**Author:** ![bskaggs](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ab097/32.png) [@bskaggs](https://www.ruby-forum.com/u/bskaggs)\
**Post date:** [September 23, 2008, 2:46pm UTC](https://www.ruby-forum.com/t/converting-the-following-sql/149314/2 "2008-09-23T14:46:33Z")

</div>

Outcome.find(:all, :conditions =\> ‘outcomes.outcome\_date = (select  
max(o2.outcome\_date) from outcomes o2 where o2.testrun\_id =  
outcomes.testrun\_id and o2.testcase\_id = outcomes.testcase\_id)’)

On Tue, Sep 23, 2008 at 9:31 AM, Brad S.  
[removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> Posted via [http://www.ruby-forum.com/](http://www.ruby-forum.com/).
> 
> >

–  
Maurício Linhares  
[http://alinhavado.wordpress.com/](http://alinhavado.wordpress.com/) (pt-br) | [http://blog.codevader.com/](http://blog.codevader.com/)  
(en)  
João Pessoa, PB, +55 83 8867-7208

---

<div class="post-metadata">

**Author:** ![bskaggs](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ab097/32.png) [@bskaggs](https://www.ruby-forum.com/u/bskaggs)\
**Post date:** [September 23, 2008, 3:01pm UTC](https://www.ruby-forum.com/t/converting-the-following-sql/149314/3 "2008-09-23T15:01:10Z")

</div>

Thanks.

BUt im still struggling, for example, theres a slight change:

select count(\*) from outcomes o1 where o1.outcome\_date = (select  
max(o2.outcome\_date) from outcomes o2 where o2.testrun\_id =  
o1.testrun\_id and o2.testcase\_id = o1.testcase\_id and o1.testrun\_id =  
‘31’ and o1.result\_id = ‘1’);

I want to substitute the ‘31’ with instance variable @testrun

so in your example, how do i escape the ‘’ string to insert the instance  
variable:

GUESS in CAPS:

…  
…  
o2.testcase\_id = outcomes.testcase\_id AND {OUTCOMES.TESTRUN\_ID =\>  
@TESTRUN} and outcomes.result\_id = “1”)’)

thanks though, i now understand alot more!

---

<div class="post-metadata">

**Author:** ![bskaggs](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ab097/32.png) [@bskaggs](https://www.ruby-forum.com/u/bskaggs)\
**Post date:** [September 23, 2008, 3:35pm UTC](https://www.ruby-forum.com/t/converting-the-following-sql/149314/4 "2008-09-23T15:35:52Z")

</div>

On Sep 23, 2008, at 8:35 AM, Maurício Linhares wrote:

> > select \* from outcomes o1 where o1.outcome\_date = (select  
> > max(o2.outcome\_date) from outcomes o2 where o2.testrun\_id =  
> > o1.testrun\_id and o2.testcase\_id = o1.testcase\_id);
> > 
> > Outcome.count ??
> 
> Maurício Linhares  
> [http://alinhavado.wordpress.com/](http://alinhavado.wordpress.com/) (pt-br) | http://  
> [blog.codevader.com/](http://blog.codevader.com/) (en)  
> João Pessoa, PB, +55 83 8867-7208

On Sep 23, 2008, at 9:01 AM, Brad S. wrote:

> ## thanks though, i now understand alot more!

Building on Maurício’s response:

Outcome.find(:all, :conditions =\> [‘outcomes.outcome\_date = (SELECT  
MAX(o2.outcome\_date) FROM outcomes AS o2 WHERE o2.testrun\_id =  
outcomes.testrun\_id AND o2.testcase\_id = outcomes.testcase\_id)  
AND outcomes.testrun\_id = ? AND outcomes.result\_id = ?’,  
@testrun, ‘1’])

You were close. Look at the docs for the conditions value, the  
form of an array is:  
["sql fragment with placeholders’,  
placeholder\_1\_value, placeholder\_2\_value]

-Rob

Rob B. [http://agileconsultingllc.com](http://agileconsultingllc.com)  
removed\_email\_address@domain.invalid