Eliminating Duplicates 1

[dobe1]

I am attempting in SQL ONLY to eliminate any duplicate records.
I have the following two fields, with which I am working.
Dynamap_id is the field, which is duplicated as many a three times per dynamap_id number. The other field is recnum. This field is unique, and will later aid me is eliminating the duplicates by forming a outside join between the query I am trying to build and the table with the duplicates.

I have tried a grouping with a "max(dynamap_id)". This does not work, because I must also must include the recnum in the group by clause. This causes the duplicates to remain. Perhaps a subquery would work best, but I am not sure just how to build it.

Any help would be appricated.

Dobe

 

[dobe1]

I looked for a way to edit, but did not see it. So, I will append. The max(dynamap_id) was actually a "first(dynamap_id), and was placed in the select statement such as:

Select dynamap_id, recnum, first(dynamap_id)
from aug0804
group by dynamap_id, recnum

This really doesn't work as you can see, because I simply get the same duplicates, because I am including the recnum. My attempt is to somehow have the recnum included, because it is the truely unique field.

Dobe

 

[PHV]

Something like this ?
SELECT dynamap_id, First(recnum) As recnum1
FROM aug0804
GROUP BY dynamap_id;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dobe1]

Thanks PHV. I realize how simple that was for you, but for some reason, I went brain dead on that one.

Dobe