sqlite subqueries with group_concat as columns in select statements

Question

I have two tables, one contains a list of items which is called watch_list with some important attributes and the other is just a list of prices which is called price_history. What I would like to do is group together 10 of the lowest prices into a single column with a group_concat operation and then create a row with item attributes from watch_list along with the 10 lowest prices for each item in watch_list. First I tried joins but then I realized that the operations where happening in the wrong order so there was no way I could get the desired result with a join operation. Then I tried the obvious thing and just queried the price_history for every row in the watch_list and just glued everything together in the host environment which worked but seemed very inefficient. Now I have the following query which looks like it should work but it's not giving me the results that I want. I would like to know what is wrong with the following statement:

select w.asin,w.title,
  (select group_concat(lowest_used_price) from price_history as p 
    where p.asin=w.asin limit 10) 
  as lowest_used 
    from watch_list as w

Basically I want the limit operation to happen before group_concat does anything but I can't think of a sql statement that will do that.

Answer 1

Never mind as somebody once said "All problems in computer science can be solved by another level of indirection." and in this case an extra select subquery did the trick:

select w.asin,w.title,
  (select group_concat(lowest_used_price) 
     from (select lowest_used_price from price_history as p 
       where p.asin=w.asin limit 10)) as lowest_used 
  from watch_list as w