Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to return email id if it contains dell.com otherwise any one email in the group Expand / Collapse
Author
Message
Posted Tuesday, July 23, 2013 8:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, September 28, 2014 12:47 PM
Points: 141, Visits: 282
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)
2 aillow@dell.com
3 laxmg@dell.com
4 zyx@dd.com (any one doesn't matter since there is no email contains dell.com)


Thank you very much in advance
asitti
Post #1476626
Posted Tuesday, July 23, 2013 9:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, September 28, 2014 12:47 PM
Points: 141, Visits: 282
Could somebody please help me

Thank you in advance
asitti
Post #1476633
Posted Tuesday, July 23, 2013 9:14 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:29 AM
Points: 1,678, Visits: 19,554


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;



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1476646
Posted Tuesday, July 23, 2013 9:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, September 28, 2014 12:47 PM
Points: 141, Visits: 282
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
Post #1476648
Posted Tuesday, July 23, 2013 9:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 13,085, Visits: 12,551
asita (7/23/2013)
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


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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1476659
Posted Tuesday, July 23, 2013 10:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, September 28, 2014 12:47 PM
Points: 141, Visits: 282
Mark Thank you very much for your example

it worked very well for me

i greatful to you,

Post #1476711
Posted Wednesday, July 24, 2013 4:57 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
@ 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.

Post #1476983
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse