CODEWITHSUNDEEP
sqlpostgresqlduplicates
Seb
Seb

Reputation: 21

Remove column duplicates with union join postgresql

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?

Upvotes: 2

Views: 1670

Answers (1)

Joost Döbken
Joost Döbken

Reputation: 4007

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.

Upvotes: 3

Related Questions