title: making a query for stock/price trend in mysql
body: <p><a href="http://sqlfiddle.com/#!2/7cf6cb/4" rel="nofollow">SQL Fiddle</a></p>
  
  <p>Table scheme:</p>
  
  <pre><code>CREATE TABLE company
      (`company_id` int,`name` varchar(30))
  ;
  
  INSERT INTO company
      (`company_id`,`name`)
  VALUES
      (1,"Company A"),
      (2,"Company B")
  
  ;
  
  CREATE TABLE price
      (`company_id` int,`price` int,`time` timestamp)
  ;
  
  INSERT INTO price
      (`company_id`,`price`,`time`)
  VALUES
      (1,50,'2015-02-21 02:34:40'),
      (2,60,'2015-02-21 02:35:40'),
      (1,70,'2015-02-21 05:34:40'),
      (2,120,'2015-02-21 05:35:40'),
      (1,150,'2015-02-22 02:34:40'),
      (2,130,'2015-02-22 02:35:40'),
      (1,170,'2015-02-22 05:34:40'),
      (2,190,'2015-02-22 05:35:40')
  </code></pre>
  
  <p>I'm using Cron Jobs to fetch company prices. In concatenating the price history for each company, how can I make sure that only the last one in each day is included? In this case, I want all of the price records around <code>05:30am</code> concatenated.</p>
  
  <p>This is the result I'm trying to get (I have used Date(time) to only get the dates from the timestamps):</p>
  
  <pre><code>COMPANY_ID          PRICE            TIME
  1                   70|170   2015-02-21|2015-02-22  
  2                  120|190   2015-02-21|2015-02-22
  </code></pre>
  
  <p>I have tried the following query but it doesn't work. The prices don't correspond to the dates and I don't know how to exclude all of the <code>2:30 am</code> records before applying the <code>Group_concat</code> function.</p>
  
  <pre><code>SELECT company_id,price,trend_date FROM
     ( 
       SELECT  company_id, GROUP_CONCAT(price SEPARATOR'|') AS price,
       GROUP_CONCAT(trend_date SEPARATOR'|') AS trend_date
       FROM 
       (
         SELECT company_id,price,
                DATE(time) AS trend_date 
         FROM price
         ORDER BY time ASC
       )x1
       GROUP BY company_id
     )t1
  </code></pre>
  
  <p>Can anyone show me how to get the desired result?</p>