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
精彩评论