Select query to retrieve distinct email address

Question

Post reply

Select query to retrieve distinct email address

elango_t1

SSC Veteran

Points: 264
More actions
July 24, 2002 at 9:34 am

#101207

I have a huge table with firstnames, lastnames, email address and unique ID associated with every record. Some email addresses are identical, so i need to select distinct email address along with First and Last names.HOW DO I DO THIS ... PLEASE HELP

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

select distinct firstname

, lastname

, email

from table b

If you need to remove duplicates, then you want a subquery that basically matches up the minimum ID (or max id) for each email.

Try it and post some code.

Steve Jones

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones

ojn Say Hey Kid Points: 673 More actions · Answer 2

select *

from tb t1

where id=(select min(id) --or max

from tb t2

where t2.email=t1.email)

--

-oj

Rac v2.1 Public Beta RELEASED.

http://www.rac4sql.net

elango_t1 SSC Veteran Points: 264 More actions · Answer 3

Thankyou. I found it easier to do the querying from Visual Basic. Just grouped the data by Email field. Compared ist record with the next record. If email matched, then skipped record else inserted it into a new table which now listed all non duplicate email addresses.

VB Code is given below

While Not rs.EOF

sEmailCurrent = CheckString(rs("Email"))

If (Len(sEmailOld) = Len(sEmailCurrent)) And (InStr(1, UCase(sEmailCurrent), UCase(sEmailOld)) > 0) Then ' if length of previous email is equal to current email

sEmailOld = CheckString((sEmailCurrent))

iPersonID = rs("Person_ID")

sLastName = CheckString(rs("LastName"))

sFirstName = CheckString(rs("FirstName"))

rs.MoveNext 'Dont insert and Move to Next Record

Else

' New email address does not match previous record. so insert into TempEmailFinal table

sEmailOld = CheckString(sEmailCurrent)

iPersonID = rs("Person_ID")

sFirstName = CheckString(rs("FirstName"))

sLastName = CheckString(rs("LastName"))

rs.MoveNext 'Move to Next Record

sSql = "Insert Into TempEmailFinal(Person_ID, LastName, FirstName, Email) Values(" & iPersonID & ", '" & sLastName & "', '" & sFirstName & "', '" & sEmailOld & "');"

m_oConn.Execute (sSql)

End If

Wend

quote:
select distinct firstname
, lastname
, email
from table b
If you need to remove duplicates, then you want a subquery that basically matches up the minimum ID (or max id) for each email.
Try it and post some code.
Steve Jones
sjones@sqlservercentral.com
http://www.sqlservercentral.com/columnists/sjones