Duplication of Data Problem

SQL Server Developer >> Duplication of Data Problem

Dear All,
I am migrating data over to a new system written in a
completely different lauguage, the database is not SQL but
one completely different.

In this DB we have a persons table, which can be
summerised as follows; -

1 Smith Peter
2 Smith Tracy
3 Smith Tony
4 Smith Mary
5 Smith Peter
6 Smith Mary
7 Jones Matt
8 Jones George
9 Jones Henry
10 Jones George

I need SQL to return the first occurances of the
duplicates ID, so I am afrer sql which will return 1, 2,
3, 4, 7, 8, 9.

Can anyone help ?

Thanks
J

SQL Server186

SQL Server Developer >> Duplication of Data Problem Julie,

Assuming the IDs are guaranteed to be unique, this should do the trick:

SELECT LastName, FirstName, MIN(ID)
FROM Persons
GROUP BY LastName, FirstName

HTH,
Nicole

> Dear All,
> I am migrating data over to a new system written in a
> completely different lauguage, the database is not SQL but
> one completely different.
>
> In this DB we have a persons table, which can be
> summerised as follows; -
>
> 1 Smith Peter
> 2 Smith Tracy
> 3 Smith Tony
> 4 Smith Mary
> 5 Smith Peter
> 6 Smith Mary
> 7 Jones Matt
> 8 Jones George
> 9 Jones Henry
> 10 Jones George
>
> I need SQL to return the first occurances of the
> duplicates ID, so I am afrer sql which will return 1, 2,
> 3, 4, 7, 8, 9.
>
> Can anyone help ?
>
> Thanks
> J

SQL Server Developer >> Duplication of Data Problem Julie
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)

INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)

PRINT 'Table'
SELECT * FROM #Demo

PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)

PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)

DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)

PRINT 'Cleaned-up Table'
SELECT * FROM #Demo

DROP TABLE #Demo

> Dear All,
> I am migrating data over to a new system written in a
> completely different lauguage, the database is not SQL but
> one completely different.
>
> In this DB we have a persons table, which can be
> summerised as follows; -
>
> 1 Smith Peter
> 2 Smith Tracy
> 3 Smith Tony
> 4 Smith Mary
> 5 Smith Peter
> 6 Smith Mary
> 7 Jones Matt
> 8 Jones George
> 9 Jones Henry
> 10 Jones George
>
> I need SQL to return the first occurances of the
> duplicates ID, so I am afrer sql which will return 1, 2,
> 3, 4, 7, 8, 9.
>
> Can anyone help ?
>
> Thanks
> J

SQL Server Developer >> Duplication of Data Problem I hope after you move this data out of SQL into your promised land, you will
use some sort of good design and implementation to prevent duplicates in the
first place (like in SQL a UNIQUE constraint.)

I also hope that you are not leaving SQL behind for something completely
different because of problems like this. Otherwise you will be back in the
same boat again.
--
----------------------------------------------------------------------------
-----------

Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)

> Julie,
>
> Assuming the IDs are guaranteed to be unique, this should do the trick:
>
> SELECT LastName, FirstName, MIN(ID)
> FROM Persons
> GROUP BY LastName, FirstName
>
> HTH,
> Nicole
>
>

> > Dear All,
> > I am migrating data over to a new system written in a
> > completely different lauguage, the database is not SQL but
> > one completely different.
> >
> > In this DB we have a persons table, which can be
> > summerised as follows; -
> >
> > 1 Smith Peter
> > 2 Smith Tracy
> > 3 Smith Tony
> > 4 Smith Mary
> > 5 Smith Peter
> > 6 Smith Mary
> > 7 Jones Matt
> > 8 Jones George
> > 9 Jones Henry
> > 10 Jones George
> >
> > I need SQL to return the first occurances of the
> > duplicates ID, so I am afrer sql which will return 1, 2,
> > 3, 4, 7, 8, 9.
> >
> > Can anyone help ?
> >
> > Thanks
> > J
>
>