ruby on rails - SQL group uniquely by type and by position -

given dataset:

id  type_id  position 1   2        7 2   1        2 3   3        5 4   1        1 5   3        3 6   2        4 7   2        6 8   3        8 

(there 3 different possible type_ids) i'd return dataset 1 of each type_id in groups, ordered position.

so grouped so:

results (id): [4, 6, 5], [2, 7, 3], [null, 1, 8] 

so first group consist of each of entries type_id's highest (relative) position score, second group have second highest score, third consist of 2 entries (and null) because there not 3 more of each type_id

does make sense? , possible?

something that:

with cte (   select       row_number() on (partition type_id order position) row_num,       *   test ) select array_agg(id order type_id) cte group row_num 

sql fiddle

of, if absolutely need nulls in arrays:

with cte (     select         row_number() on (partition type_id order position) row_num,         *     test ) select array_agg(c.id order t.type_id) (select distinct row_num cte)     cross join (select distinct type_id test) t     left outer join cte c on c.row_num = a.row_num , c.type_id = t.type_id group a.row_num 

sql fiddle