Duplicate record search

Or in Oracle

SELECT A.* FROM TABLE1 A,TABLE1 B
WHERE A.COL1=B.COL1 AND A.ROWID<>B.ROWID

Steve

Sorry should have said SQL server.  I am trying to find any duplicate URL's in my links database.

well similar to nitin..
Select URLField, Count(URLField) as cnt from YourTable Order by cnt desc group by URLField having cnt > 1

Silvers5

When using the following code I get this error:-
strQuery = "Select Link, Count(Link) as cnt from Rlinks Order by cnt desc group by Link having cnt > 1"

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'group'.

The Order By needs to be at the end as follows:

strQuery = "Select Link, Count(Link) As cnt From Rlinks Group By Link Having cnt > 1 Order By cnt Desc"

Anthony

Thats worked. I am using this to display the duplicates:-

While Not objRS.EOF
response.write objrs("Link")
objRS.MoveNext
Wend

However it only shows 1 URL that I have duplicated (Which I expected).  How would i display the Record ID of both the duplicated records So I could write a peice of code to delete the unrequired one?

Sorry I commented out the wrong line it didn't work. Error:-

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'cnt'.

/test/showlinks.asp, line 14

When error comes, we should also know the strQuery value. As it is the key to the answer.

Regarding your comment (10:17am pst) above, you will need to do it as a separate task. We will advice as to how to when we sort out your recent problem first.

It was the string that was suggested by acperkins
strQuery = "Select Link, Count(Link) As cnt From Rlinks Group By Link Having cnt > 1 Order By cnt Desc"

Error:-
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'cnt'.

/test/showlinks.asp, line 14

The working query string is as follows. I have tested it locally.

Select Link, Count(Link) As cnt
From Rlinks
Group By Link
Having Count(Link) > 1
Order By 2 Desc

Sorry about that, that should have been:
strQuery = "Select Link, Count(Link) As Cnt From Rlinks Group By Link Having Count(Link) > 1 Order By cnt Desc"

Anthony

If the order is important than it gets a little more complicated, but very doable (I will break it down to make it more readable):
Select Rlinks.*
From   Rlinks
       Inner Join (Select Link,
                    Count(Link) As Cnt
                   From       Rlinks
                   Group By
                    Link
                   Having Count(Links) > 1) A On Rlinks.Link = A.Link
Order By
       A.Cnt Desc

You can of course limit the fields to just the one you need as follows (for example):

Select Rlinks.ID,
       Rlinks.Link
From   Rlinks
       Inner Join (Select Link,
                    Count(Link) As Cnt
                   From       Rlinks
                   Group By
                    Link
                   Having Count(Links) > 1) A On Rlinks.Link = A.Link
Order By
       A.Cnt Desc

Run these queries in the Query Analyzer first and you will see it will give you the right results.

Anthony

Hopefully this looks better:
Select     Rlinks.ID,
               Rlinks.Link
From          Rlinks
               Inner Join     (
                                   Select     Link,
                                                  Count(Link) As Cnt
                                   From          Rlinks
                                   Group By Link
                                   Having Count(Links) > 1) A On Rlinks.Link = A.Link
Order By
               A.Cnt Desc

Anthony

Anthony your post of 02:14PM PST works fine. (I have not tried the other posts because order does not matter ~ However when I have time I will play around with it.)

I did try to get Rlinks.ID. But came back with "Invalid column name 'ID'".  That would work if I was using access because that field is manditory, however I am using SQL server Is there an internal uniquie field that I could use?

Pete

Unfortunately, there in no internal unique field that i know of in SQL Server which you can use as key. You will need to create a surrogate key.

However, it will be great to see the list of columns that you have at present. One MIGHT turn out to be unique.

And if there is no other information attached to the two separate rows of same url, how does it matter which one you delete?

>>That would work if I was using access because that field is manditory, however I am using SQL server Is there an internal uniquie field that I could use?<<

In this aspect Access is no different from SQL Server.  If there is a column called ID in SQL Server (or Access) than you should be able to display with:

strQuery = "Select * From Rlinks Where Link In (Select Link From Rlinks Group By Link Having Count(Link) > 1)"

Or explicitly with:
strQuery = "Select ID, Link From Rlinks Where Link In (Select Link From Rlinks Group By Link Having Count(Link) > 1)"

Anthony