-1

I have a table that contains a list of names used to designate objects (represented themselves by an id). It is created with:

CREATE TABLE all_names
(
    id       integer NOT NULL,
    name     varchar(64) PRIMARY KEY,
    priority integer NOT NULL,
);

(The priority field is mostly used to sort the list of synonyms of an object with given id.)

I would like to create from this table another table that associates to each object its name with highest priority. Columns should be like this:

CREATE TABLE objs
(
    id       integer PRIMARY KEY,
    name     varchar(64) NOT NULL UNIQUE,

);

How can I fill the new table with only the top names?

Example

all_names:

id name priority
----------------
1  A    3
1  AA   2
1  AAA  1

2  B    1


3  C    4
3  CC   3
3  CCC  2
3  CCCC 1

should give me:

objs:

id name
-------
1  A
2  B
3  C

as the name of object 1 with highest priority is A, etc.

EDIT

Fiddle that also includes proposed solutions

Tony
  • 101
  • 4

2 Answers2

0
WITH cte AS ( SELECT id, name, 
                     ROW_NUMBER() OVER (PARTITION BY id ORDER BY priority DESC) rn
              FROM all_names )
INSERT INTO objs (id, name) 
SELECT id, name
FROM cte
WHERE rn = 1
Akina
  • 20,750
  • 2
  • 20
  • 22
0

Simply use distinct on like below

insert into objs
select distinct on (id) id,name 
from all_names 
order by id, priority desc

DEMO

Akhilesh Mishra
  • 180
  • 8