CODEWITHSUNDEEP
sqlpostgresql
cetver
cetver

Reputation: 11829

postgresql select query

create temp table tmp_apps (
  id integer
);

create temp table tmp_pos (
     tmp_apps_id integer,
     position integer
);

insert into tmp_apps
select 1 id union
select 2 id 
;

insert into tmp_pos (tmp_apps_id, position)
select 1 tmp_apps_id, 1 as position union all
select 1 tmp_apps_id, 1 as position union all
select 1 tmp_apps_id, 2 as position union all
select 1 tmp_apps_id, 3 as position union all
select 1 tmp_apps_id, 3 as position union all
select 2 tmp_apps_id, 1 as position
;
/*
Expected result:
tmp_apps_id tmp_pos_position
1           1,2
2           1
*/

How to get first 2 comma separated, distinct tmp_pos.position for each tmp_apps.id
It is possible ?

Upvotes: 2

Views: 674

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658927

WITH x AS (
    SELECT tmp_apps_id
         , position
         , row_number() OVER (PARTITION BY tmp_apps_id ORDER BY position) AS rn
    FROM   tmp_pos
    GROUP  BY 1, 2
    ORDER  BY 1, 2
    )
SELECT tmp_apps_id, string_agg(position::text, ', ')
FROM   x
WHERE  rn < 3
GROUP  BY 1;

This happens to be much like the solution @araqnid posted a bit faster than me.
CTE or subquery, that's just two ways for doing the same in this case.

My version is different in one important aspect:
By using GROUP BY instead of DISTINCT to get distinct values, you can apply the window function row_number() (key element for the solution) in the same query level and do not need another subquery (or CTE).

The reason for this is that aggregation (GROUP BY) is applied before window functions while DISTINCT is applied after. In many situations DISTINCT and GROUP BY offer equally good solutions. In a case like this you can put the subtle difference to good use if you know it. I expect this to be quite a bit faster.

Upvotes: 1

araqnid
araqnid

Reputation: 133792

select tmp_apps_id, string_agg(position::text,',')
from (
 select tmp_apps_id, position,
        row_number() over (partition by tmp_apps_id order by position)
 from (
  select distinct tmp_apps_id, tmp_pos.position from tmp_pos
 ) x
) x
where row_number <= 2
group by tmp_apps_id;

Upvotes: 1

p.campbell
p.campbell

Reputation: 100637

Try this using array_agg or string_agg, depending on your version of Postgres:

SELECT tmp_apps_id, array_agg(tmp_pos_position)
FROM tmp_pos_position
GROUP BY tmp_apps_id

In 9.0, use the string_agg function:

SELECT tmp_apps_id, string_agg(tmp_pos_position, ',')
FROM tmp_pos_position
GROUP BY tmp_apps_id

Upvotes: 0

Related Questions