title: sqlite subqueries with group_concat as columns in select statements
body: <p>I have two tables, one contains a list of items which is called <code>watch_list</code> with some important attributes and the other is just a list of prices which is called <code>price_history</code>. 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 <code>watch_list</code> along with the 10 lowest prices for each item in <code>watch_list</code>. 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 <code>price_history</code> for every row in the <code>watch_list</code> 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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Basically I want the <code>limit</code> operation to happen before <code>group_concat</code> does anything but I can't think of a sql statement that will do that.</p>