1

I would like to perform a query like

SELECT * FROM ( SELECT * FROM products ORDER BY price ASC ) AS s GROUP BY item;

which return the cheapest of all products for each item. Using this subquery is good because it can run in O(N logN) time.

So I can find this with find_by_sql, but it would be nice to be able to chain it with other scopes for Product.

Anyone know how to either write this as a scope or chain scoped and find_by_sql?

genkilabs
  • 2,966
  • 30
  • 36

1 Answers1

6

You should be able to do something like

Product.from("(SELECT * FROM products ORDER BY price ASC) AS products").group(:item)
simonwh
  • 1,017
  • 8
  • 21
  • Awesome, thanks, this mostly works... EXCEPT as it turns out, the ORDER BY and GROUP BY in sqlite3 works differently than in mysql. So in MySQL you get the highest price, but in my sqlite3 dev environment you get the bottom most element of the group, so the lowest price. lol I guess that is really another problem entirely tho. – genkilabs Feb 02 '11 at 21:05