Construct new table by dropping rows that don't have highest “priority”

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,

(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:

    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 A, etc.


Fiddle that also includes proposed solutions

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.

Method 1

Simply use distinct on like below

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


Method 2

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 🙂

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply