CODEWITHSUNDEEP
sqloracle-database
Kevin Jones
Kevin Jones

Reputation: 429

Selecting from table where a name appears twice

I want to select from a table where a name appears twice.

For example I have a table like this,

ID          Name
----       ------
1          Jane John
2          Kevin Smith
3          Jane John

What I want is for the output to show where Jane John appear twice so it should look something like this:

ID          Name
----       ------
1          Jane John
3          Jane John

I tried looking around on stackoverflow but couldn't find an exact and easy answer.

I'm using oracle SQL Developer.

Upvotes: 1

Views: 8694

Answers (1)

Horaciux
Horaciux

Reputation: 6477

You ask for a record that appears twice. If a row appears three times it won't show unless you modify the having clause as commented.

SELECT id
    ,NAME
FROM tablen
WHERE NAME IN (
        SELECT NAME
        FROM TableN n
        GROUP BY (NAME)
        HAVING counT(NAME) = 2 --Use >1 instead of =2 for more than one record
        )

EDIT

I'll add a new solution in regard to your last comment. As you can only ask for one field in IN() I'll use a special character or string making sure it does not belongs to valid values in any field. Look at this: http://sqlfiddle.com/#!6/2af55/3

SELECT id
    ,NAME
    ,name2
FROM tablen
WHERE concat(NAME,'=',name2) IN (
        SELECT concat(NAME,'=',name2)
        FROM TableN n
        GROUP BY concat(NAME,'=',name2)
        HAVING count(concat(NAME,'=',name2)) = 2
        )

Note I wrote this thinking in SQL Server, not sure if concat function works as well in Oracle or look for an alternative.

Upvotes: 4

Related Questions