I can't get the following SQL query to work quite right in Rails. It runs, but it fails to do the "HAVING row_number = 1" part, so I'm getting all the records, instead of just the first record from each group. A quick description of the query: it is finding hotel deals with various criteria, and in particular, priortizing them being paid, and then picking the one with the highest dealrank. So, if there are paid deal(s), it'll take the highest one of those (by dealrank) first, if no paid deals, it takes the highest dealrank unpaid deal for each hotel. Using MAX(dealrank) or something similar does not work as a way to pick off the first row of each hotel group, which is why I have the enclosing temptable and the creation of the row_number column. Here's the query:
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'm currently using Rails' find_by_sql to do this, although I've also tried putting it into a regular find using the :select, :from, and :having parts (but :having won't get used unless you have a :group as well). If there is a different way to write this query, that'd be good to know too.
I am using Rails 2.3.5, MySQL 5.0.x.
