SQL Queries to find double entries

Alternative 1

SELECT [field2], count(*)
FROM (
SELECT DISTINCT [field1],[field2]
FROM [table1]
) vwTmp
GROUP BY [field2]
HAVING count(*) > 1
ORDER BY [field2]

Alternative 2

SELECT [field1], [field2]
FROM [table1]
WHERE [field2] IN
(
SELECT DISTINCT main.[field2]
FROM [table1] main
INNER JOIN [table1] sub
ON sub.[field2] = main.[field2]
AND sub.[field1] <> main.[field1]
)

Alternative 3

SELECT [field1], [field2]
FROM [table1] main
WHERE EXISTS
(
SELECT [field1]
FROM [table1] alias
WHERE alias.[field2] = main.[field2]
AND alias.[field1] <> main.[field1]
)
ORDER BY [field2],[field1]

comments

No comments yet.

Sorry, the comment form is closed at this time.

Laatste notities

Categoriën