SQL group by with ungrouped columns

Question

I have a log table with the following structure:

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
);

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

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).

here is sql fiddle of the example table and my query.

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

I need these columns for several joins

UPD:
Thanks to Roman Pekar, his answer led me to the right path. I use his second query to write my final queries.

First: select average accept/done time, max/min accept/done time respectively by departments, and select oid and cid with max accept time in each department

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

Second: similar to the first but select all these values for organizations (oid)

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

Answer 1

Don't know what are you trying to do with your query, but it's really overcomplicated. You first query could be done much simplier with window functions and without joins:

with cte as (
    select
        oid, department, cid,
        max(case when status=1 then recordtime end) as open,
        max(case when status=2 then recordtime end) as accept,
        max(case when status=3 then recordtime end) as done
    from mytable
    group by oid, department, cid
)
select
    department, oid,
    extract(epoch from avg(accept - open)) as a_time,
    extract(epoch from avg(done - open)) as d_time,
    extract(epoch from max(accept - open)) as max_a_time,
    extract(epoch from max(done - open)) as max_d_time
from cte
group by department, oid
order by department, oid;

sql fiddle demo

If you want to get cid from which you get max_time, you can use distinct on syntax:

with cte as (
    select
        oid, department, cid,
        max(case when status=1 then recordtime end) as open,
        max(case when status=2 then recordtime end) as accept,
        max(case when status=3 then recordtime end) as done
    from mytable
    group by oid, department, cid
)
select distinct on (department, oid)
    department, oid, cid,
    extract(epoch from accept - open) as a_time
from cte
order by department, oid, accept - open desc;

or use ranking function row_number():

with cte as (
    select
        oid, department, cid,
        max(case when status=1 then recordtime end) as open,
        max(case when status=2 then recordtime end) as accept,
        max(case when status=3 then recordtime end) as done
    from mytable
    group by oid, department, cid
), cte2 as (
    select
        department, oid, cid,
        accept, open,
        row_number() over(
              partition by department, oid
              order by accept - open desc
        ) as rn
    from cte
)
select
    department, oid, cid,
    extract(epoch from accept - open) as a_time
from cte2
where rn = 1
order by department, oid

sql fiddle demo