sql server programming: select duplicate records -- sql server programming

Re: select duplicate records

vanitha
10/5/2005 10:31:02 PM

i just gave an example with 2 columns. In the real situation i have n number
of columns and also there is a int data type in some columns. so i can't use
max, min or any other aggregate functions.

is there any other solution?

thanks
vanitha

[quoted text, click to view]

RE: select duplicate records

R.D
10/5/2005 11:27:03 PM

vanitha
It is related to your previous post:
how did you solve your migration problem,Actually Iwas thinking of that.
What came to my my mind was this:
In those situations you can disallow duplicate values, by using IGNORE_DUP_KEY
which ignores dup keys. and continues with next insert or update statement.
E. Use the IGNORE_DUP_KEY
This example creates a unique clustered index on the emp_pay table. If a
duplicate key is entered, the INSERT or UPDATE statement is ignored.

SET NOCOUNT ON
USE pubs
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'emp_pay')
DROP TABLE emp_pay
GO
USE pubs
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'employeeID_ind')
DROP INDEX emp_pay.employeeID_ind
GO
USE pubs
GO
CREATE TABLE emp_pay
(
employeeID int NOT NULL,
base_pay money NOT NULL,
commission decimal(2, 2) NOT NULL
)
INSERT emp_pay
VALUES (1, 500, .10)
INSERT emp_pay
VALUES (2, 1000, .05)
INSERT emp_pay
VALUES (3, 800, .07)
INSERT emp_pay
VALUES (5, 1500, .03)
INSERT emp_pay
VALUES (9, 750, .06)
GO
SET NOCOUNT OFF
GO
CREATE UNIQUE CLUSTERED INDEX employeeID_ind
ON emp_pay(employeeID)
WITH IGNORE_DUP_KEY

--
Regards
R.D
--Knowledge gets doubled when shared

[quoted text, click to view]

RE: select duplicate records

vanitha
10/5/2005 11:39:03 PM

i decided to do the migration step by step.

first to find all the duplicate records in all the master tables and
eliminate it by moving it to the error table.
then export the master tables.
same way script to check the referential integrity, move those records into
the error table to eliminate the same. then export the transaction tables.

i am also thinking about some effective method to do migration.

if u get any idea abt it... please share with me.

thanks
vanitha

[quoted text, click to view]

Re: select duplicate records

David Portas
10/6/2005 12:00:00 AM

How about posting accurate DDL so that we don't have to guess? Always
include key(s) with your DDL scripts. If the table doesn't have a key then
say so.

Does the table have a primary key? If so, maybe you can do the following:

SELECT id1, name1
FROM test1 AS T
WHERE key_col =
(SELECT MIN(key_col)
FROM test1
WHERE id1 = T.id1) ;

If the table doesn't have a key and you are attempting a clean-up then you
can temporarily add an IDENTITY key for the purposes of this query - bearing
in mind that the result will be an arbitrary row for each distinct ID1.

--
David Portas
SQL Server MVP
--

[quoted text, click to view]

Re: select duplicate records

David Portas
10/6/2005 12:00:00 AM

Small amendment:

SELECT id1, name1
FROM test1 AS T
WHERE key_col =
(SELECT MIN(key_col)
FROM test1
WHERE id1 = T.id1
HAVING COUNT(*)>1) ;

--
David Portas
SQL Server MVP
--

[quoted text, click to view]

Re: select duplicate records

David Portas
10/6/2005 12:00:00 AM

SELECT id1, MAX(name1) AS name1
FROM test1
GROUP BY id1
HAVING COUNT(*)>1 ;

--
David Portas
SQL Server MVP
--

Re: select duplicate records

vanitha
10/6/2005 12:08:02 AM

Now I want to delete the same records...

how to query that?

thanks
vanitha

[quoted text, click to view]

Re: select duplicate records

David Portas
10/6/2005 12:42:41 AM

[quoted text, click to view]

Are you sure that's what you mean? My previous query only returns 1 row
per ID1 but as you still haven't specified a key perhaps there could be
2, 3 or more duplicates. So if you deleted just the same rows returned
by my query you could still be left with duplicates.

Even if I'm wrong, hopefully you are learning why proper specifications
are important - unfortunately you still haven't posted any. Read the
following article:
http://www.aspfaq.com/etiquette.asp?id=5006

Here's a wild, unsupported guess at what you require. Make sure you do
a backup and test it out before you use it for real:

DELETE FROM test1
WHERE EXISTS
(SELECT *
FROM test1 AS T
WHERE T.id1 = test1.id1
AND T.key_col < test1.key_col) ;

--
David Portas
SQL Server MVP
--

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.