Remove column duplicates with union join postgresql

Question

I want to hide duplicate columns in my postgresql select statement. Here is the sql code:

SELECT name, amount, cpu, id 
FROM computersystem, stock 
WHERE cpu=id 
UNION 
SELECT name, amount, ram, id 
FROM computersystem, stock 
WHERE ram=id 
ORDER BY name, amount

And the current output:

Name:AMD Ultimate Overkill amount:2
Name:AMD Ultimate Overkill amount:10
Name:CPU Heavy, Low Graphics amount:2
Name:CPU Heavy, Low Graphics amount:10
Name:Graphics Heavy, Low CPU amount:8
Name:Graphics Heavy, Low CPU amount:10

What I need to get is only 1 of each name

Name:AMD Ultimate Overkill amount:2
Name:CPU Heavy, Low Graphics amount:2
Name:Graphics Heavy, Low CPU amount:8

Is there a quick fix for this?

Community · Accepted Answer · 2017-05-23 12:31:17Z

3

DISTINCT ON might solve your issue.

 SELECT DISTINCT ON (name)
        name,
        amount,
        cpu,
        id
 FROM computersystem, stock 
 WHERE cpu=id 
 UNION SELECT name, amount, ram, id FROM computersystem, stock 
 WHERE ram=id
 GROUP BY name
 ORDER BY name, amount

Find the official documentation here.

edited May 23, 2017 at 12:31

CommunityBot

11 silver badge

answered Apr 5, 2016 at 4:14

Joost Döbken

4,1476 gold badges50 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Remove column duplicates with union join postgresql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related