Very Very URGENT..pleaseeeeee

Question

Post reply

Very Very URGENT..pleaseeeeee

Bhuvnesh

SSC Guru

Points: 59351
More actions
June 22, 2009 at 3:42 am

#103766

CREATE TABLE TEST
(
acct_id int ,
email_alert_stub uniqueidentifier,
email varchar(100),
seq_no int
)
insert into test
VALUES(2133,'D189110E-6551-4AE8-B8B9-DDCA9D885BE8','mlu@l.mail')
insert into test
VALUES(2133,'D189110E-6551-4AE8-B8B9-DDCA9D885BE8','bkumar@cv')
insert into test
VALUES(2133,'D189110E-6551-4AE8-B8B9-DDCA9D885BE8','bhuv@wdc')
insert into test
VALUES(2133,'D189110E-6551-4AE8-B8B9-DDCA9D885BE8','aaa@qqq')
DROP table test
my required is
data should be like this:
2000133D189110E-6551-4AE8-B8B9-DDCA9D885BE8mlu@l.mail1
2000133D189110E-6551-4AE8-B8B9-DDCA9D885BE8bkumar@cv2
2000133D189110E-6551-4AE8-B8B9-DDCA9D885BE8bhuv@wdc3
2000133D189110E-6551-4AE8-B8B9-DDCA9D885BE8aaa@qqq4
how would i generate (seq no column values)(ex: 1 ,2 ,3 ,4 above shown)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 8 posts - 1 through 7 (of 7 total)

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

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Depends it you need to store the value or not,

presumably seq_no is partitioned by seqno so an identity column will not be suitable ? or will it ?

Try

Select acct_id ,email_alert_stub ,email, row_number() over (partition by acct_id order by acct_id) as seq_no

from test

Clear Sky SQL
My Blog[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 2

SELECT LEFT(acct_id,1) + '000' + RIGHT(acct_id,3),

email_alert_stub,

email,

ROW_NUMBER() OVER(ORDER BY email DESC) AS seq

FROM TEST

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

chrisandsarah Newbie Points: 3 More actions · Answer 3

Hello,

Depending on your requirements I'd probably make that seq_no field an identity column.

CREATE TABLE dbo.test ( acct_id INT

, email_alert_stub UNIQUEIDENTIFIER

, email VARCHAR(100)

, seq_no INT IDENTITY(1,1))

That means you can still run your insert code and it will automatically populate your 1, 2, 3, 4 etc. Again all comes down to your requirements, if you've a seq_no field you might as well populate it.

Cheers,

Chris

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

thanks a lottttttttttttttttttttt :-):-):-):-):-)

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

LutzM SSC Guru Points: 107049 More actions · Answer 5

Great you found a solution that worked for you. But which one was it?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 6

i tried your solution and it worked fine

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Sinshith S Anand SSChasing Mays Points: 605 More actions · Answer 7

Try this modified Query..........

CREATE TABLE TESTo

(

acct_id int ,

email_alert_stub uniqueidentifier,

email varchar(100),

seq_no int identity(1,1) not null

)

insert into testo

VALUES(2133,'D189110E-6551-4AE8-B8B9-DDCA9D885BE8','mlu@l.mail')

insert into testo

VALUES(2133,'D189110E-6551-4AE8-B8B9-DDCA9D885BE8','bkumar@cv')

insert into testo

VALUES(2133,'D189110E-6551-4AE8-B8B9-DDCA9D885BE8','bhuv@wdc')

insert into testo

VALUES(2133,'D189110E-6551-4AE8-B8B9-DDCA9D885BE8','aaa@qqq')

select * from testo