Kevin Jones Kevin Jones - 11 months ago 90
SQL Question

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.

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download