sql - what is the quickest way to run a query to find where 2 fields are the same -

i have table id, first, last , want run query says

give me every record combination of first , last exists more once

(i trying find duplicate records)

edit

concatenation give out false answers pointed out in comments ('roberto neil' vs 'robert oneil'.

here answer eliminates concatenation issue. found out non duplicates , eliminated them final answer.

with mytable (     select 1 id, 'john' firstname, 'doe' lastname     union     select 2 id, 'john' firstname, 'doe' lastname     union     select 3 id, 'tim' firstname, 'doe' lastname     union     select 4 id, 'jane' firstname, 'doe' lastname     union     select 5 id, 'jane' firstname, 'doe' lastname ) select id, firstname, lastname mytable selecttable id not in (     select min (id)     mytable searchtable     group firstname, lastname     having count (*) = 1 ) 

---

old solution

use group , having.. check out working sample

with mytable ( select 1 id, 'john' firstname, 'doe' lastname union select 2 id, 'john' firstname, 'doe' lastname union select 3 id, 'time' firstname, 'doe' lastname union select 4 id, 'jane' firstname, 'doe' lastname ) select id, firstname, lastname mytable firstname + lastname in (     select firstname + lastname     mytable     group firstname + lastname     having count (*) > 1 ) 

this result in following

id          firstname lastname ----------- --------- -------- 1           john      doe 2           john      doe