More Efficient Query Needed

[sitechooser]

Hi All

Does anyone have an idea how to run this query...

SELECT * 
FROM [TableName] 
WHERE [ColumnName] IN
(
      SELECT [ColumnName] 
      FROM [TableName] 
      GROUP BY [ColumnName] 
      HAVING COUNT(*) > 1 
)
ORDER BY [ColumnName]

without the sub query?

The sub query option seems to be taking ages to run. Is there a more efficient query that does the same thing?

Thanks in advance for any help.

[sunfighter]

SELECT [ColumnName]
FROM [TableName]
GROUP BY [ColumnName] <= Not needed because that is the only thing your getting
HAVING COUNT(*) > 1 <= This makes no sense to me because count() tells you the number of rows. Do you mean where the value of the ColumnName column is large then 1?

[sitechooser]

Sorry, it would help if I explained what I'm trying to do!

The query is supposed to search a column (name) for duplicate values and then return all data in the table which has duplicate values in that column

Any ideas?

[Old Pedant]

Nope, I think you have it right.

Though doing it like this *MIGHT* be more efficient:

SELECT T1.*
FROM table AS T1,
     ( SELECT column, COUNT(column) AS cnt
       FROM table 
       GROUP BY column
       HAVING cnt > 1
    ) AS T2
WHERE T1.column = T2.column
ORDER BY T1.column

Or even possibly (though I doubt it)

SELECT T1.*
FROM table AS T1,
     ( SELECT column, COUNT(column) AS cnt
       FROM table 
       GROUP BY column
    ) AS T2
WHERE T1.column = T2.column AND T2.cnt > 1
ORDER BY T1.column

May not matter, but you could try it.

[Old Pedant]

It will, of course, make a *HUGE* difference if column is indexed.

[sitechooser]

Magic!

I ran the query on an 80MB table and it returned in 6.5 minutes. I then made the column concerned an index, ran the same query and it returned in 7.6 seconds! Bit of a difference

Thanks a million for your help, got a critical meeting tomorrow morning and you'll make me look like superman.

[Old Pedant]

LOL! The cardinal rule of database design: When it's slow, index it.