November 30, 2010 at 10:59 am
Hi,
I have the following:
ID Name Email
-----------------------
1 a.Smith asmih@abc.com
1 a.b.Smith asmih@abc.com
A.Smith is the same person and I only want to extract his email address. But because the names are different I am getting the second record. Is there an easy way to show only one record and suppress the other? I tried group by but because I need the name, it shows up twice.
Thanks
November 30, 2010 at 11:02 am
The best solution would be to fix the data structure so you have the name separate from the e-mail address. That allows 1 person to have multiple addresses, if needed (I have 5 that I use regularly, my wife has dozens, maybe hundreds, or at least 5 or 6).
If you can't normalize the data, then you'll need to pick just one row. Do you have the actual table structure available to post here? If so, I can show you actual code. If not, take a look at either select max() on the name, or using one of the ranking functions (row_number, rank, dense_rank) in a CTE and picking one row per.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 1, 2010 at 6:41 am
That's really bad design KS. Anyway, check this out, if you don't want to change the table structure.
if (object_id('tempdb..#contact') is not null) drop table #contact
create table #contact (id smallint, name varchar(50), email varchar(50))
insert into #contact
select 1, 'a.Smith', 'asmih@abc.com'
union
select 1, 'a.b.Smith', 'asmih@abc.com'
;with ctecontact as
(
select id,
from
#contact
group by id,
)
select
c.id,
stuff((select ', ' + [name] from #contact where id = c.id for xml path('')),1,2,'') [Names],
--(select top 1 [name] from #contact where id = c.id) [Name],
c.email
from ctecontact c
- arjun
https://sqlroadie.com/
December 1, 2010 at 6:57 am
Rather than working around the problem why not just fix up the data?
Is there a primary Key on the table as from the 2 rows youve provided the only key that can exist is a composite of all three columns.
If there isnt a primary key then delete the entry you dont need. Both have the ID as 1 so it should affect any referencing.
December 1, 2010 at 7:00 am
Thank You!
December 2, 2010 at 9:45 am
If the email address is all you need, you could try this
SELECT DISTINCT(Email) from YourTable
WHERE ID = '1'
or this but you will probably have mulitple rows returned so it would be better by ID
SELECT DISTINCT(Email) from YourTable
WHERE Name like ('%Smith')
Good Luck!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply