How to insert many records excluding some in PostgreSQL

Question

I want to create a table with a subset of records from a master table. for example, I have:

id  name   code  ref
1   peter  73    2.5
2   carl   84    3.6
3   jack   73    1.1

I want to store peter and carl but not jack because has same peter's code. I need the max ref!

I try this:

SELECT id, name, DISTINCT(code) INTO new_tab
FROM old_tab 
WHERE (conditions)

but it doesn't work.

Answer 1

You can use window functions for this:

select t.id, t.name, t.code, t.ref
from (select t.*,
             row_number() over (partition by code order by ref desc) as seqnum
      from old_tab t
     ) t
where seqnum = 1;

The insert statement just wraps insert around this:

insert into new_tab(id, name, code)
    select t.id, t.name, t.code
    from (select t.*,
                 row_number() over (partition by code order by ref desc) as seqnum
          from old_tab t
         ) t
    where seqnum = 1;