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

i have a table with id, first, last and i want to run a query that says

开发者_JAVA百科

give me every record where the combination of first and last exists more than once

(i am trying to find duplicate records)

---

EDIT

Concatenation will give out false answers as pointed out in the comments ('Roberto Neil' vs 'Robert ONeil'.

Here is an answer that eliminates the concatenation issue. I found out the non duplicates and eliminated them from the final answer.

WITH MyTable AS
(
    SELECT 1 as ID, 'John' as FirstName, 'Doe' as LastName
    UNION
    SELECT 2 as ID, 'John' as FirstName, 'Doe' as LastName
    UNION
    SELECT 3 as ID, 'Tim' as FirstName, 'Doe' as LastName
    UNION
    SELECT 4 as ID, 'Jane' as FirstName, 'Doe' as LastName
    UNION
    SELECT 5 as ID, 'Jane' as FirstName, 'Doe' as LastName
)
SELECT Id, FirstName, LastName
FROM MyTable SelectTable
WHERE Id Not In
(
    SELECT Min (Id)
    From MyTable SearchTable
    GROUP BY FirstName, LastName
    HAVING COUNT (*) = 1
)

---

OLD SOLUTION

Use GROUP BY and HAVING.. check out this working sample

WITH MyTable AS
(
SELECT 1 as ID, 'John' as FirstName, 'Doe' as LastName
UNION
SELECT 2 as ID, 'John' as FirstName, 'Doe' as LastName
UNION
SELECT 3 as ID, 'Time' as FirstName, 'Doe' as LastName
UNION
SELECT 4 as ID, 'Jane' as FirstName, 'Doe' as LastName
)
SELECT ID, FirstName, LastName
FROM MyTable
WHERE FirstName + LastName IN
(
    SELECT FirstName + LastName
    FROM MyTable
    GROUP BY FirstName + LastName
    HAVING COUNT (*) > 1
)

This will result in the following

ID          FirstName LastName
----------- --------- --------
1           John      Doe
2           John      Doe

---

You can also use windowing functions. This will perform slightly better than Raj More's solution:

with MyTable as
(
    select 1 as ID, 'John' as FirstName, 'Doe' as LastName
    union
    select 2 as ID, 'John' as FirstName, 'Doe' as LastName
    union
    select 3 as ID, 'Time' as FirstName, 'Doe' as LastName
    union
    select 4 as ID, 'Jane' as FirstName, 'Doe' as LastName
)
select * 
from (
    select *, cnt = count(*) over ( partition by FirstName, LastName )
    from MyTable
) x
where x.cnt > 1

---

Here are two possible solutions. Which is faster will likely depend on your indexes and data, so try both and see which works better for you. In most cases though, the first query will be faster I believe.

SELECT
    T1.id
FROM
    My_Table T1
INNER JOIN
(
    SELECT
        first_name,
        last_name
    FROM
        My_Table T2
    GROUP BY
        first_name,
        last_name
    HAVING
        COUNT(*) > 1
) SQ ON
    SQ.first_name = T1.first_name AND
    SQ.last_name = T1.last_name

SELECT
    T1.id
FROM
    My_Table T1
WHERE
    EXISTS
    (
        SELECT *
        FROM
            My_Table T2
        WHERE
            T2.first_name = T1.first_name AND
            T2.last_name = T1.last_name AND
            T2.id <> T1.id
    )

---

SELECT count(*) FROM table HAVING count(*) > 1 GROUP BY concat(first, last)

---

Untested:

SELECT name, count(*) from (
   SELECT id, first+last as [name]
   from table) t
HAVING count(*) >1