DISTINCT PROBLEM

i have two fields. One is id (otomatic number) and another is email..
there are some duplicate records in email records.
I want to use distinct query for email field to eliminate duplicate
records..I also want to get id fields of resultset of distinct email.
I thought that there are more than one id value for duplicate records. Can i
get max or min id valure of duplicate records or what algorithm sql server
does use ?Maybe something like...
SELECT email, MAX([ID]) AS [ID]
FROM table
GROUP BY email
ORDER BY email
HTH,
Ben
"Savas Ates" <in da club> wrote in message
news:OtQCQLyNGHA.916@.TK2MSFTNGP10.phx.gbl...
>i have two fields. One is id (otomatic number) and another is email..
> there are some duplicate records in email records.
> I want to use distinct query for email field to eliminate duplicate
> records..I also want to get id fields of resultset of distinct email.
> I thought that there are more than one id value for duplicate records. Can
> i
> get max or min id valure of duplicate records or what algorithm sql server
> does use ?
>|||Untested...
Select A.ID, A.EMAIL from MYTABLE A
where exists (select 1 from MYTABLE B
where B.EMAIL = A.EMAIL
and B.ID < A.ID)
This should select all rows with duplicate EMAIL except for the EMAIL with
the lowest numbered ID.
You could also use:
select A.EMAIL, count(*) from MYTABLE A
group by A.EMAIL
having COUNT(*) > 1
Which will give you a list of all EMAILs occuring more than once, along with
how many times they occur. After you ge the data cleaned up you can add a
unique constraint which will prevetn duplicate emails from getting inserted
in the future.
"Savas Ates" <in da club> wrote in message
news:OtQCQLyNGHA.916@.TK2MSFTNGP10.phx.gbl...
> i have two fields. One is id (otomatic number) and another is email..
> there are some duplicate records in email records.
> I want to use distinct query for email field to eliminate duplicate
> records..I also want to get id fields of resultset of distinct email.
> I thought that there are more than one id value for duplicate records. Can
i
> get max or min id valure of duplicate records or what algorithm sql server
> does use ?
>sql