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:

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

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

DEMO

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 stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply