title: amazon-redshift : select id, first(a),sum(b) group by id
body: <p>In mysql/SparkSQL we have <code>first</code> function. which doesn't exist in Redshift.</p>
  
  <p>I have to change code </p>
  
  <pre><code>SELECT
    product_id,
    first(product_code) as product_code,
    first(product_name) as product_name,
    first(time_date) as time_date, 
    max(price_max) as price_max,
    min(price_min) as price_min,
    sum(count_of_sales) as count_of_sales,
    SUM(CASE WHEN time_date = 1538323200000 THEN cost_of_stock_start ELSE 0 END) as cost_of_stock_start,
  from storeproductincomelogs 
  WHERE time_date&gt;= 1538323200000 
    AND time_date&lt;= 1541001600000 
  group by product_id;
  </code></pre>
  
  <p>to </p>
  
  <pre><code>SELECT
    product_id,
    product_code,
    product_name,
    min(time_date) as time_date,  # have to change first to min, this column can't group by
    max(price_max) as price_max,
    min(price_min) as price_min,
    sum(count_of_sales) as count_of_sales,
    SUM(CASE WHEN time_date = 1538323200000 THEN cost_of_stock_start ELSE 0 END) as cost_of_stock_start,
  from storeproductincomelogs 
  WHERE time_date&gt;= 1538323200000 
    AND time_date&lt;= 1541001600000 
  group by product_id,product_code,product_name;
  </code></pre>
  
  <p>Without <code>first</code>, I have to add <code>product_code,product_name</code> in group by clasuse.
  Or would get error:</p>
  
  <blockquote>
    <p>Invalid operation: column "storeproductincomelogs.product_code" must appear in the GROUP BY clause or be used in an aggregate function;</p>
  </blockquote>
  
  <p>Note: here <code>product_id,product_code</code> are unique in each row, and duplicated <code>product_name</code> is also hardly found (but may be appear in future so I think I can't use group by).</p>
  
  <hr>
  
  <p>I searched mysql <code>first</code> equivalent in postgresql and got <a href="https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group">Select first row in each GROUP BY group?</a> .</p>
  
  <p>First, I tried <a href="https://stackoverflow.com/a/7630564/1637673"><code>Distinct on</code> clause</a> which is not supported in Redshift. </p>
  
  <p>Then, I tried:</p>
  
  <pre><code>WITH summary AS (
      SELECT product_id,
             product_code,
             product_name,
              min(time_date) as time_date,
              max(price_max) as price_max,
              sum(count_of_sales) as count_of_sales,
              SUM(CASE WHEN time_date = 1538323200000 THEN cost_of_stock_start ELSE 0 END) as cost_of_stock_start,
             ROW_NUMBER() OVER(PARTITION BY product_id ) AS rk
        FROM  storeproductincomelogs)
  SELECT *
    FROM summary
   WHERE rk = 1;
  </code></pre>
  
  <p>got error </p>
  
  <blockquote>
    <p>[42803][500310] Amazon Invalid operation: column "storeproductincomelogs.product_id" must appear in the GROUP BY clause or be used in an aggregate function;</p>
  </blockquote>
  
  <p>I don't know how to write correct one, so can't test the performance. </p>
  
  <p>How can I do this in Redshift?</p>