Preserve the order by ids in postgresql with DISTINCT-CodePudding

I have a query, which returns a simple list of numbers:

SELECT unnest(c) FROM t ORDER BY f LIMIT 10;

And it goes like

I want to keep the result unique, but also preserve order:

select distinct(id) from (select ...) as c;

does not work, beacuse it uses HashAggregate, which breaks order (and processes all rows to return just 10?). I tried GROUP BY, it also uses HashAggregate the whole table(?) and then sort and return 10 required rows.

Is it possible to do it effectively on DB size? Or should I just read rows from my first query in my application and do the stream filtering?

CodePudding user response：

with ordinality is your friend to preserve the order.

select val 
from unnest('{1,1,3,4,2,3,5,1,5,6,3,2}'::int[]) with ordinality t(val, ord)
group by val
order by min(ord); -- the first time that this item appeared

val
1
3
4
2
5
6

Or it may make sense to define this function:

create function arr_unique(arr anyarray) 
returns anyarray language sql immutable as
$$
select array_agg(val order by ord)
from
(
 select val, min(ord) ord 
 from unnest(arr) with ordinality t(val, ord)
 group by val
) t;
$$;

CodePudding user response：

select elem
from (
  select 
  elem, elem_no, row_no, row_number() over (partition by elem order by row_no) as occurence_no
  from (
     select elem, elem_no, row_number() over () as row_no from t, unnest(c) WITH ORDINALITY a(elem, elem_no)
  ) A 
) B 
where occurence_no = 1
order by row_no