sql - Ruby on Rails get records by highest price -

i have model rails model called orders has type_id, location, , price. each type can have multiple orders @ same location different prices. check below idea of table structure.

id | type_id | location_id | price ----------------------------------- 1  | 1       | 1           | 12 2  | 1       | 1           | 14 3  | 1       | 1           | 9 4  | 2       | 1           | 1 5  | 2       | 1           | 4 6  | 3       | 1           | 15 7  | 3       | 1           | 7 

i wanting select records type_id using in example:

type_ids = "1,2,3" location_id = 1  order.find(:all, :conditions => ["location_id = ? , type_id in (?)", location_id, type_ids]) 

i want select record highest price each type in location, result set bring records id 2, 5 , 6.

i can not seem work out find query this, hope understand mean. if not ask can try , explain better.

cheers

eef

if want single highest-price conditions, use

order.find(:first, :conditions => ["location_id = ? , type_id in (?)", location_id, type_ids], :order => 'price desc') 

...because query order matching records highest lowest price, give first record (highest price).

if want highest-price item each type, you're breaking out of rails' query building , using find_by_sql get max(price) each instance of type_id (something select *, max(price) orders location_id = ? , type_id in (?) group type_id, i'd want run query few times against database refine it, myself).

unless number of type_ids large (e.g. in hundreds) might simpler run above query once each type , put results in 1 array; mysql query faster, particularly large numbers of type_ids, doing in rails enough unless you're determined max(price) query right.