Duplicate Records

[Gregory Taylor]

Yes, I know this subject has been exhausted, but I need help in locating the discussion which took place a few months ago.
Sharon relayed to the group a piece of software (expensive) which would help in my particular situation. I grabbed a demo and have gotten the approval for purchase. Unfortunately, I don't have the thread with me at work.

The problem:

Number Fname Lname Age ID
123 John Franklin 43 1
123 Jane Franklin 40 2
123 Jeff Franklin 12 3
124 Jean Simmons 39 4
125 Gary Bender 37 5
126 Fred Johnson 29 6
126 Fred Johnson 39 7
127 Gene Simmons 47 8

The idea would be to get only unique records from the Number column. I don't care about which information I grab from the other columns, but I must have those fields included.
If my resultant result set looked as follows, that would be fine. Or any other way, as long as all of the fields had information and there were only unique values in the Number field.

Number Fname Lname Age ID
123 Jeff Franklin 12 3
124 Jean Simmons 39 4
125 Gary Bender 37 5
126 Fred Johnson 39 7
127 Gene Simmons 47 8

If anyone remembers this discussion, mainly the date, I would really appreciate it.

Thanks

Gregory Taylor
MIS Director
Timeshares By Owner

[Karl]

Why do you want to buy an expensive piece of software when this isn't really that difficult a problem? The SQL code shouldn't take long to write. Of course, if you want to pay me an expensive amount then I could do it for you. .

Basically, just find out which numbers are in duplicate, in the example below 126 and 123. Set the rowcount to 1 and insert the whole row into a temp table. Set the rowcount back to zero and the insert all other rows (the ones with no duplicates) into the temp table. Select * from temptable.

Sorry if I've missed something,

Karl

------------
Gregory Taylor at 6/12/2002 10:30:44 AM

Yes, I know this subject has been exhausted, but I need help in locating the discussion which took place a few months ago.
Sharon relayed to the group a piece of software (expensive) which would help in my particular situation. I grabbed a demo and have gotten the approval for purchase. Unfortunately, I don't have the thread with me at work.

The problem:

Number Fname Lname Age ID
123 John Franklin 43 1
123 Jane Franklin 40 2
123 Jeff Franklin 12 3
124 Jean Simmons 39 4
125 Gary Bender 37 5
126 Fred Johnson 29 6
126 Fred Johnson 39 7
127 Gene Simmons 47 8

The idea would be to get only unique records from the Number column. I don't care about which information I grab from the other columns, but I must have those fields included.
If my resultant result set looked as follows, that would be fine. Or any other way, as long as all of the fields had information and there were only unique values in the Number field.

Number Fname Lname Age ID
123 Jeff Franklin 12 3
124 Jean Simmons 39 4
125 Gary Bender 37 5
126 Fred Johnson 39 7
127 Gene Simmons 47 8

If anyone remembers this discussion, mainly the date, I would really appreciate it.

Thanks

Gregory Taylor
MIS Director
Timeshares By Owner