Find Rows where the Same Two Column Values Recur

sql, sql-server

Solution

It would seem more natural to me to write:

SELECT A, B, Count(*) FROM MyTable
GROUP BY A, B
HAVING COUNT(*) > 1

And it's the most efficient way of doing it (and so is the query in the question).

Similarly to the above query, you can rewrite your second query:

SELECT A + ',' + B, Count(*) FROM MyTable
GROUP BY A, B
HAVING COUNT(*) > 1

Problem

Given a table in SQL-Server like: ``` Id INTEGER A VARCHAR(50) B VARCHAR(50) -- Some other columns ``` with no index on A or B, I wish to find rows where a unique combination of A and B occurs more than once. I'm using the query ``` SELECT A+B, Count(A+B) FROM MyTable GROUP BY A+B HAVING COUNT(A+B) > 1 ``` First Question Is there a more time-efficient way to do this? (I cannot add indices to the database) Second Question When I attempt to gain some formatting of the output by including a `,` in the concatenation: ``` SELECT A+','+B, Count(A+','+B) FROM MyTable GROUP BY A+','+B HAVING COUNT(A+','+B) > 1 ``` The query fails with the error ``` Column 'MyDB.dbo.MyTable.A' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause ``` with a similar error for Column B. How can I format the output to separate the two columns?

Original source