All we need is an easy explanation of the problem, so here it is.
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, );
priority field is mostly used to sort the list of synonyms of an object with given
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?
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:
id name ------- 1 A 2 B 3 C
as the name of object
1 with highest priority is
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
distinct on like below
insert into objs select distinct on (id) id,name from all_names order by id, priority desc
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
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂