Finding rows that have the same value with Postgres

Question

Not sure if the title is descriptive enough (sorry about the bad english), but my problem is that I need to find the rows that have the same value in all rows.

Possibly easier to understand what I actually mean with an example: I have an table that's called Catalogue with the attributes motortype, motorpart and partnumber where motortype and motorpart are VARCHAR and partnumber are int.

Some partnumbers are used in all the motortypes, and it's those partnumbers that I am interested in finding.

I'm having a hard time seeing how to solve this, so any help would be greatly appreciated!

EDIT: Sorry if the question was lackful or bad. I have updated with the table schema, some sample data and my desired results under:

CREATE TABLE Catalogue (
motortype VARCHAR,
motorpart VARCHAR,
partnumber int,
PRIMARY KEY (partnumber)
);

Sample data:

INSERT INTO Catalogue VALUES ('M1', 'Brakes', 1);
INSERT INTO Catalogue VALUES ('M2', 'Brakes', 1);
INSERT INTO Catalogue VALUES ('M3', 'Brakes', 1);
INSERT INTO Catalogue VALUES ('M1', 'Gasket', 2);
INSERT INTO Catalogue VALUES ('M2', 'Gasket', 2);
INSERT INTO Catalogue VALUES ('M3', 'Pedal', 3);

Desired result:

| motorpart | partnumber |
|-----------|------------|
| Brakes    |          1 |

Hope this was better formulated!

Please add your table schema, some sample data and the desired result. — McNets
– McNets, Commented Apr 29, 2017 at 18:32

McNets · Accepted Answer · 2017-04-29 18:55:03Z

1

If there are 3 different types you can use next query:

select   motorpart
from     Catalogue
group by motorpart
having   count(distinct motortype) >= 3;

If you don't know the number:

select   motorpart
from     Catalogue
group by motorpart
having   count(distinct motortype) = (select distinct motortype 
                                      from   Catalogue);

Rextester here

answered Apr 29, 2017 at 18:55

McNets

10.9k3 gold badges39 silver badges70 bronze badges

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

1 Comment

Breinne Over a year ago

Hi, and thanks so much for your answer! I don't know how many types of motors there are, so the second query is the one I'm looking after :) But when I try to run it, I get an errormessage: operator does not exist: bigint = character varying But I see the concept behind that query, so I'll try some more, thanks a lot!

Collectives™ on Stack Overflow

Finding rows that have the same value with Postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related