how to return email id if it contains dell.com otherwise any one email in the group
-
if an email contains %dell.com% then return it otherwise max(email) / min(email) just one
please help me,
DDL
create table #onner (acctno int, email_id varchar(30))
insert into #onner values(1,'king@dell.com')
insert into #onner values (1,'siller@dell.com')
insert into #onner values (1,'villa@billo.com')
insert into #onner values (2,'aillow@dell.com')
insert into #onner values (2,'king@tcs.com')
insert into #onner values (3,'laxmg@dell.com')
insert into #onner values (4,'abc@bb.com')
insert into #onner values (4,'zyx@dd.com')
insert into #onner values (4,'WWW@xx.com')
insert into #onner values (4,'ZAS@vonage.com')
expected results
acctno Email
1 siller@dell.com (any dell.com if exist more than once)
4 zyx@dd.com (any one doesn't matter since there is no email contains dell.com)
Thank you very much in advance
asitti
-
Could somebody please help me
Thank you in advance
asitti
-
WITH CTE AS (SELECT acctno , email_id,
ROW_NUMBER() OVER(PARTITION BY acctno ORDER BY CASE WHEN email_id LIKE '%dell.com%' THEN 0 ELSE 1 END, email_id) AS rn
FROM #onner)
SELECT acctno , email_id
FROM CTE
WHERE rn=1
ORDER BY acctno;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
is it something can be done in sql, or do we need to do step by step process for this
i am trying hard but no use, could somebody help me please
-
asita (7/23/2013)
is it something can be done in sql, or do we need to do step by step process for thisi am trying hard but no use, could somebody help me please
What is wrong with example Mark provided? It does exactly what you said it needs to do.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Mark Thank you very much for your example
it worked very well for me
i greatful to you,
-
@ asita,
Do you understnad what Marks code does. If not you need to have it explained; don't just use it if you can't replicate it.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply