|
| 
| Submitted on: 11/10/2000 4:00:14 PM
By: Greg
Level: Beginner
User Rating:
By 8 Users
Compatibility:SQL Server 7.0
Users have accessed this code 6286 times.
| |
| | | Allows you to determine duplicate rows in a table and provide a count of the duplicates.
It's helped me find a row or two that had left me with "inconclusive" results in the past : )
Ciao;
Greg
| |
| |
|
code:
Can't Copy and Paste this?
Click here for a copy-and-paste friendly version of this code!
Terms of Agreement:
By using this code, you agree to the following terms...
1) You may use
this code in your own programs (and may compile it into a program and distribute it in compiled format for langauges that allow it) freely and with no charge.
2) You MAY NOT redistribute this code (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
3) You may link to this code from another website, but ONLY if it is not wrapped in a frame.
4) You will abide by any additional copyright restrictions which the author may have placed in the code or code's description. |
--**************************************
--
-- Name: FindDupes
-- Description:Allows you to determine d
-- uplicate rows in a table and provide a c
-- ount of the duplicates.
It's helped me find a row OR two that had LEFT me WITH "inconclusive" results in the past : )
Ciao;
Greg
-- By: Greg
--
-- Assumes:Limiting the columns provides
-- field level duplicate finds..
Using ALL columns will give a row LEVEL duplicate find.
--
--This code is copyrighted and has-- limited warranties.Please see http://
-- www.Planet-Source-Code.com/xq/ASP/txtCod
-- eId.247/lngWId.5/qx/vb/scripts/ShowCode.
-- htm--for details.--**************************************
--
SELECT column1, column2, column3, ... n, COUNT(*) AS [Number OF Dupes]
FROM TableName
GROUP BY column1, column2, column3... n
HAVING COUNT(*) > 1
| |
Other 1 submission(s) by this author
|
|
| |
| Report Bad Submission |
|
| |
| Your Vote! |
|
See Voting Log |
| |
| Other User Comments |
1/30/2001 4:40:38 PM:todde
does anyone know how to find the row in
the above statement with the GREATEST
NUMBER of dups without using a second
query or a view?
todde@metalwest.com
|
1/31/2001 9:36:17 AM:Greg
Try the derived table
method...
SELECT Column1
FROM
(SELECT COLUMN1, COUNT(*) AS 'Amount'
FROM TableName
GROUP BY Column1
HAVING COUNT(*) = (SELECT MAX
(B.Amount)
FROM (SELECT COUNT(*)
AS 'Amount'
FROM TableName
GROUP BY Column1
HAVING
COUNT(*) > 1) AS B)
) AS A
GROUP
BY Column1
Hope the formatting is
okay.
Ciao;
Greg
|
2/18/2001 3:20:42 AM:Brian Bender
Great Job!
Take this one step further.
How would you write a query that
deletes the dupes and leaves only one
behind?
Email me please:
brianbender77@hotmail.com
|
2/28/2001 9:23:58 AM:Greg
Sorry it took so long...
Deleting
data is always risky.
I would
suggest multi-staging an approach such
as
1) Find dupes
2) query to get PK's
based on dupes
3) delete based on PK's
-1
Bottom line... thee are many ways
this could be accomplished and they
depend on the structure of your
table(s).
Ciao;
Greg
|
|
| Add Your Feedback! |
Note:Not only will your feedback be posted, but an email will be sent to the code's author in your name.
NOTICE: The author of this code has been kind enough to share it with you. If you have a criticism, please state it politely or it will be deleted.
For feedback not related to this particular code, please click here.
|
|
