CODEWITHSUNDEEP
sqlpostgresql
JakeDrone
JakeDrone

Reputation: 183

SQL - Querying for names that occur at least twice

So I'm trying to find a way to query for a table "people" that has attribute "name", and I would like to query for names that occur at least twice, while the results should be distinct.

I was thinking of creating two alias tables, and joining on name but I can't figure it out.

Here is what I tried:

SELECT DISTINCT name
FROM people AS S1 
INNER JOIN people AS S2 USING name
WHERE S2.lastname <> S2.surname

The surname part I did to remove cases of names appearing because of the two tables being equal (not even sure if this is correct). But either way, this already failed as the syntax is wrong.

Would appreciate some help! Thanks in advance.

Upvotes: 0

Views: 682

Answers (3)

sys
sys

Reputation: 330

select p.people_name, count(1) as cnt
from people p
group by 1
having cnt >=1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271141

Aggregation is a simple method if you want just the names:

select name
from persons
group by name
having count(*) > 1;

If you want the original rows, use window functions:

select p.*
from (select p.*, count(*) over (partition by name) as cnt
      from persons p
     ) p
where cnt >= 2;

Upvotes: 2

wildplasser
wildplasser

Reputation: 44250

Simple: use EXISTS() [ you only need to select from the people table once, and you dont have to use DISTINCT ] :

SELECT *
FROM people s1 
WHERE EXISTS (SELECT *
    FROM people s2
    WHERE s2.name = s1.name
    AND S2.lastname <> S1.lastname
    );

BTW: assuming lastname <--> surname was a typo?

Upvotes: 1

Related Questions