Cannot convert SQL statements (uses HAVING clause and temp table) to Rails Rails regular lookup.

I am unable to get the following SQL to work in Rails. It works, but it doesn't do the "HAVING row_number = 1" part, so I get all the records, not just the first record from each group. Quick description of the request: search for hotel offers by various criteria and, in particular, give them payment, and then select the one with the highest contract. Thus, if there is a paid deal (s), she takes the highest (contract) deal first, if there are no paid deals, she takes the highest unpaid deal for each hotel. Using MAX (dealrank) or something similar doesn't work as a way to select the first row of each hotel group, so I have the tempting temptation to create a row_number column. Please note that this query works correctly when run directly in MySQL.Here's the request:

SELECT *, 
       @num := if(@hid = hotel_id, @num + 1, 1) as row_number,
       @hid := hotel_id as dummy
  FROM (
    SELECT hotel_deals.*, affiliates.cpc,
           (CASE when affiliates.cpc > 0 then 1 else 0 end) AS paid
      FROM hotel_deals 
      INNER JOIN hotels ON hotels.id = hotel_deals.hotel_id  
      LEFT OUTER JOIN affiliates ON affiliates.id = hotel_deals.affiliate_id 
      WHERE ((hotel_deals.percent_savings >= 0) AND 
             (hotel_deals.booking_deadline >= ?))   
      GROUP BY hotel_deals.hotel_id, paid DESC, hotel_deals.dealrank ASC) temptable 
  HAVING row_number = 1

      

        
        
        
      

    

I am currently using Rails for find_by_sql, although I also tried to put it in a normal find using the following elements: select ,: from and: having (but: getting no use unless you also have a group :). If there is another way to write this query, it would be good to know.

I am using Rails 2.3.5, MySQL 5.0.x.