title: SQL group by with ungrouped columns
body: <p>I have a log table with the following structure:</p>
  
  <pre><code>CREATE TABLE mytable (
      oid        integer(10),
      department  integer(10),
      cid         integer(10),
      status      integer(1) comment 'statuses: 1-open, 2-accept, 3-done',
      recordtime  datetime
  );
  </code></pre>
  
  <p>This table stores some data about status assignment. oid - organization, cid - card id.
  Organizations belogns to departments, when organization update card(set new status) in this table inserts row</p>
  
  <p>I try to select statistic data from that table, e.g.: select max/min accept time, max/min done time and average accept/done time by department or by organization (oid).</p>
  
  <p><a href="http://sqlfiddle.com/#!1/94015/8" rel="nofollow">here is sql fiddle of the example table and my query</a>.</p>
  
  <p>The problem is how to get cid in selected columns when group by department, oid and oid and cid when group by department. In other words: i want to know organization (oid) and card id (cid) with, for instance, max accept time when i select grouped rows</p>
  
  <p>I need these columns for several joins</p>
  
  <p><strong>UPD:</strong><br>
  Thanks to <strong>Roman Pekar</strong>, his answer led me to the right path. I use his second query to write my final queries.</p>
  
  <p>First: select average accept/done time, max/min accept/done time respectively by departments, and select <code>oid</code> and <code>cid</code> with max accept time in each department</p>
  
  <pre><code>with cte as (
      select
          oid, cid,
          max(case when status=1 then recorddatetime end) as open,
          max(case when status=2 then recorddatetime end) as accept,
          max(case when status=3 then recorddatetime end) as done
      from 
          mytable
      group by oid, cid
      having 
          max(case when status=1 then recorddatetime end) is not null and max(case when status=2 then recorddatetime end) is not null
          and max(case when status=3 then recorddatetime end) is not null
      order by oid, cid
  )
  select distinct on(department)
      department, oid, cid,
      ceil(extract(epoch from avg(cte.accept - cte.open) over (partition by department))) as avg_accept_time,
      ceil(extract(epoch from avg(done - open) over (partition by department))) as avg_done_time,
      ceil(extract(epoch from max(accept - open) over (partition by department))) as max_accept_time,
      ceil(extract(epoch from max(done - open) over (partition by department))) as max_done_time,
      ceil(extract(epoch from min(accept - open) over (partition by department))) as min_accept_time,
      ceil(extract(epoch from min(done - open) over (partition by department))) as min_done_time
  from cte cte
  order by department, max_accept_time desc
  </code></pre>
  
  <p>Second: similar to the first but select all these values for organizations (<code>oid</code>)</p>
  
  <pre><code>with cte as (
          select
              oid, cid,
              max(case when status=1 then recorddatetime end) as open,
              max(case when status=2 then recorddatetime end) as accept,
              max(case when status=3 then recorddatetime end) as done
          from 
              mytable
          group by oid, cid
          having 
              max(case when status=1 then recorddatetime end) is not null and max(case when status=2 then recorddatetime end) is not null
              and max(case when status=3 then recorddatetime end) is not null
          order by oid, cid
      )
      select distinct on(department, oid)
          department, oid, cid,
          ceil(extract(epoch from avg(cte.accept - cte.open) over (partition by department, oid))) as avg_accept_time,
          ceil(extract(epoch from avg(done - open) over (partition by department, oid))) as avg_done_time,
          ceil(extract(epoch from max(accept - open) over (partition by department, oid))) as max_accept_time,
          ceil(extract(epoch from max(done - open) over (partition by department, oid))) as max_done_time,
          ceil(extract(epoch from min(accept - open) over (partition by department, oid))) as min_accept_time,
          ceil(extract(epoch from min(done - open) over (partition by department, oid))) as min_done_time
      from cte cte
      order by department, oid, max_accept_time desc
  </code></pre>