July 24, 2002 at 9:34 am
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
July 24, 2002 at 10:01 am
select distinct firstname
, lastname
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
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 24, 2002 at 6:03 pm
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.
July 30, 2002 at 1:18 pm
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
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
http://qa.sqlservercentral.com/columnists/sjones
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply