DISTINCT OR GROUP BY

I have some records in my Sql Db.
There are some duplicate record in my email and adsoyad fields
Email Adsoyad
a@.a.com Savas
a@.a.com Pele
b@.b.com Savas
c@.c.om Ilim
d@.d.com Hasan
d@.d.com Hasan
I want to eliminate email address which can be duplicate and its Adsoyad
field.
There can be different adsoyad records for duplicate email records. I want
to select one of them which doesnt have any importance for me .
I also have soma email records which doesnt have @. character. I want to
eliminate those records too. Is there any command in SQL like INSTR ?Do you want to delete the row or update the fields?
For deleting the rows with email without '@.'
Delete from table where email not like '_%@._%'
I put the additional requirement that there must be at least one char in
front of and 1 char behind the @. sign...
The second thing you wanted to do is delete the dupe emails by picking the
Adsoyad which does not have meaning for you... You'll have to repst and
describe how you determine which Adsoyad doesn't have meaning and someone
will help you with the SQL...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Savas Ates" wrote:

> I have some records in my Sql Db.
> There are some duplicate record in my email and adsoyad fields
> Email Adsoyad
> a@.a.com Savas
> a@.a.com Pele
> b@.b.com Savas
> c@.c.om Ilim
> d@.d.com Hasan
> d@.d.com Hasan
> I want to eliminate email address which can be duplicate and its Adsoyad
> field.
> There can be different adsoyad records for duplicate email records. I want
> to select one of them which doesnt have any importance for me .
> I also have soma email records which doesnt have @. character. I want to
> eliminate those records too. Is there any command in SQL like INSTR ?
>
>|||> There can be different adsoyad records for duplicate email records. I want
> to select one of them which doesnt have any importance for me .
Use DISTINCT, then you can tell from your query that that is your purpose.
GROUP BY is typically used for aggregation.

> I also have soma email records which doesnt have @. character. I want to
> eliminate those records too. Is there any command in SQL like INSTR ?
Yes, look at CHARINDEX, PATINDEX. You might also consider a function or
even a check constraint that actually validates the format of an e-mail
address. Then you can't get any crap in there in the first place. Search
groups.google.com, there are plenty of examples out there ready to use.|||IT doesnt matter which adsoyad Record im gonna choose. I want to just pick
one of adsoyad records ?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com>, haber iletisinde
unlar yazd:E9100F46-9958-4408-B527-2209F25B40B5@.microsoft.com...
> Do you want to delete the row or update the fields?
> For deleting the rows with email without '@.'
> Delete from table where email not like '_%@._%'
> I put the additional requirement that there must be at least one char in
> front of and 1 char behind the @. sign...
> The second thing you wanted to do is delete the dupe emails by picking the
> Adsoyad which does not have meaning for you... You'll have to repst and
> describe how you determine which Adsoyad doesn't have meaning and someone
> will help you with the SQL...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Savas Ates" wrote:
>|||> IT doesnt matter which adsoyad Record im gonna choose. I want to just pick
> one of adsoyad records ?
Do you need adsoyad in the result? Can you provide more clear requirements
so we don't have to ask 80 follow-up questions? Please see
http://www.aspfaq.com/5006|||Yep I need adsoyad records too. But it doesnt matter which one i can come
up. I want to elimitate email addresses which are dublicate and adsoyad
records whics is tied to one of the duplicate email addresses. I should say
that i dont need to chooese any adsoyad records exactly . Just wanna pick
up one of them .
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%2366CMQ1TGHA.1236@.TK2MSFTNGP11.phx.gbl...
> Do you need adsoyad in the result? Can you provide more clear
> requirements so we don't have to ask 80 follow-up questions? Please see
> http://www.aspfaq.com/5006
>|||(reposting using the Microsoft's site, since the Google Groups post was not
found here)
Hi, Savas
Try something like this (untested):
SELECT Email, MIN(Adsoyad)
FROM YourTable
WHERE Email LIKE '_%@._%._%' AND Email NOT LIKE '%@.%@.%'
GROUP BY Email
Note that the LIKE expression above (although more complex than what
you have suggested), still doesn't ensure a valid e-mail address (as
per RFC 822).
Razvan