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

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

distinct name and email.

hey guys

.

you see how fenix.sn has a couple of entries BUT the same email and DIFFErENT BET IDS.

okay this is what i wanna do.

i wanna send an email to fenix.sn (just ONE email) saying you have won the following bets: xxx, xxx, xxx, xxx

then i wanna delete the entreis from the table.

masfenix,

There are several ways to accomplish this. Let me give you one of them here.

SELECT
[Username],
[Useremail],
WonBetIDs = REPLACE(
(
SELECT
WonBetID AS [data()]
FROM
tblUser tIn
WHERE
tIn.[Username] = tOut.[Username]
ORDER BY
WonBetID
FOR XML PATH ('')
), ' ', ',')
FROM
tblUser tOut
GROUP BY
[Username], [Useremail]

Hope this helps.|||

thanks for that ,but i am not gonna lie. i do not understand htat code at all. i am looking for a function.

|||It's good to be frank. Here is another implementation that may help you to understand easily. Since there isn't any function that does what you had mentioned, I have UDF to do the same for you here. Hope this helps.

Function
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION dbo.getWonBetIdsOfUser
(
@.Username VARCHAR(20)
)
RETURNS VARCHAR(2048)
AS
BEGIN
DECLARE @.WonBetIds VARCHAR(2048)

SELECT
@.WonBetIds = COALESCE(@.WonBetIds + ',', '') + [WonBetID]
FROM
tblUser
WHERE
[Username] = @.Username

RETURN @.WonBetIds

END
GO

Calling function
SELECT [Username], dbo.getWonBetIdsOfUser([Username])
FROM tblUser
GROUP BY [Username]|||

how do i actually call that stored proceudre?

i dont understand the "calling function".

note: i just wanna make a button called "send email" and it should automatically go through the list and send the emails. (but once for each user)

|||Use the query specified under Calling Function part in SqlCommand in your .NET app to get a dataset/datatable containing all username/email and bet ids. You can loop thru each records in the table and mail them from within your .NET application.

Distinct email addresses in 2 tables with different field names

Hi everyone,
I have 2 tables with table A containing an 'email' field and table B
containing 2 fields 'primaryemail' and 'secondaryemail'. Now is it possible
to issue a query that would return only the unqiue email addresses in these
3 fields? So a long list with no duplicate emails(distinct).
Thank you
Maz.Hi Maz,
Please don't post questions independently in multiple newsgroups. You
question has already been answered in .programming.
--
Jacco Schalkwijk
SQL Server MVP
"Maziar Aflatoun" <maz88@.rogers.com> wrote in message
news:STSEb.32573$2We1.12257@.news04.bloor.is.net.cable.rogers.com...
> Hi everyone,
> I have 2 tables with table A containing an 'email' field and table B
> containing 2 fields 'primaryemail' and 'secondaryemail'. Now is it
possible
> to issue a query that would return only the unqiue email addresses in
these
> 3 fields? So a long list with no duplicate emails(distinct).
> Thank you
> Maz.
>
>

DISTINCT

Hi,
I need to only pull distinct values from my database ie...
SELECT DISTINCT Type, ClickID, Email, FullApp
FROM tblApps
However I also want to get other fields that also are not distinct ie the
record ID number, but if I include the ID number then I get all the rows.
How can I apply DISTINCT on just a few fields, but still return every field
in the table?
--
Regards
Gary Howlett
Systems Developer
www.rainbowgrp.co.ukHi Gary,
The question you have to ask yourself is, when you return the distinct
values from some columns, and also columns with values that are not
distinct, how do you determine which values you are going to return? If you
have a 2 rows with the same Type, ClickID, Email and FullApp, the ID of
which row do you want to return? The highest ID, the lowest ID, a random ID?
If you want the highest or the lowest you can use MAX() or MIN(), a random
one is a bit more difficult.
hth
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Gary Howlett" <gary@.rainbowgrp.co.uk> wrote in message
news:jP4%a.3830$z7.642629@.wards.force9.net...
> Hi,
> I need to only pull distinct values from my database ie...
> SELECT DISTINCT Type, ClickID, Email, FullApp
> FROM tblApps
> However I also want to get other fields that also are not distinct ie the
> record ID number, but if I include the ID number then I get all the rows.
> How can I apply DISTINCT on just a few fields, but still return every
field
> in the table?
> --
> Regards
> Gary Howlett
> Systems Developer
> www.rainbowgrp.co.uk
>|||Maybe what you're looking for is to use the GROUP BY clause. If I
understood your question, you're looking to group by a few of the fields,
and still get the other fields. Since you're grouping by some of the
fields, the other fields will have to be returned in some sort of aggregate
function.
An example would be this (run in Query Analyzer):
use northwind
select CustomerID, min(OrderDate) FirstOrderDate
from Orders
group by CustomerID
You essentially get all the "distinct" CustomerIDs, but of course any other
fields would have to be aggregated (see the BOL for the other aggregate
operations available). Every non-grouped field will have to be aggregated
in some way.
HTH
"Gary Howlett" <gary@.rainbowgrp.co.uk> wrote in message
news:jP4%a.3830$z7.642629@.wards.force9.net...
> Hi,
> I need to only pull distinct values from my database ie...
> SELECT DISTINCT Type, ClickID, Email, FullApp
> FROM tblApps
> However I also want to get other fields that also are not distinct ie the
> record ID number, but if I include the ID number then I get all the rows.
> How can I apply DISTINCT on just a few fields, but still return every
field
> in the table?
> --
> Regards
> Gary Howlett
> Systems Developer
> www.rainbowgrp.co.uk
>|||You can't expect to select distinct and select the
record_id.
The record_id is unique, therefore, distinct.
You need to understand exactly what you want to retrieve
with the query.
Regards
>--Original Message--
>Hi,
>I need to only pull distinct values from my database ie...
>SELECT DISTINCT Type, ClickID, Email, FullApp
>FROM tblApps
>However I also want to get other fields that also are not
distinct ie the
>record ID number, but if I include the ID number then I
get all the rows.
>How can I apply DISTINCT on just a few fields, but still
return every field
>in the table?
>--
>Regards
>Gary Howlett
>Systems Developer
>www.rainbowgrp.co.uk
>
>.
>