Increment ItemNo based on condtion

Question

Post reply

Increment ItemNo based on condtion

surya prakash

SSC-Addicted

Points: 455
More actions
July 27, 2009 at 11:18 am

#73303

Hi,
I have a table with two columns
CREATE TABLE [dbo].[Table2] (
[clientorder_id] [int] NULL ,
[ItemNo] [int] NULL
) ON [PRIMARY]
GO
the data is some thing like
Insert into table2 (1001,null)
Insert into table2 (1001,null)
Insert into table2 (1002,null)
Insert into table2 (1002,null)
Insert into table2 (1002,null)
Insert into table2 (1003,null)
Insert into table2 (1003,null)
Insert into table2 (1004,null)
I am looking for the out put like
1001,1
1001,2
1002,1
1002,2
1002,3
1003,1
1003,2
1004,1
Thanks
Surya

Viewing 6 posts - 1 through 5 (of 5 total)

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

matt6288 SSCertifiable Points: 5058 More actions · Answer 1

If the only thing you are concerned about it the output you could use this:

SELECT clientorder_id, ROW_NUMBER () OVER (PARTITION BY clientorder_id order BY clientorder_id)

FROM table2

However, if you have to update the table then it is a little harder since there isn't a unique id for a given row.

edit

to get the comma separation that i didn't notice before you can use this:

SELECT CONVERT(VARCHAR,clientorder_id)+','+ CONVERT(VARCHAR,ROW_NUMBER () OVER (PARTITION BY clientorder_id order BY clientorder_id))

FROM table2

Lowell SSC Guru Points: 323518 More actions · Answer 2

excellent job posting the CREATE and INSERT statemetns.

Thank you!

getting the output you want is easy; it's exactly what the new row_number() function is for.

updating the existing table is hard, unless you've got a identity column in there, as you need to join the table agaisnt itself.

SELECT [clientorder_id],

Row_number()OVER(Partition by [clientorder_id] order by [clientorder_id])As NewItemNo

From Table2

here's how you'd update, but you gotta have an identity:

CREATE TABLE [dbo].[Table2] (

Table2ID int identity(1,1),

[clientorder_id] [int] NULL ,

[ItemNo] [int] NULL

)

Insert into table2 SELECT 1001,null

Insert into table2 SELECT 1002,null

Insert into table2 SELECT 1003,null

Insert into table2 SELECT 1004,null

UPDATE Table2

SET Table2.ItemNo = MyAlias.NewItemNo

From (SELECT Table2ID,[clientorder_id],Row_number()OVER(Partition by [clientorder_id] order by [clientorder_id])As NewItemNo

From Table2) MyAlias

Where Table2.Table2ID = MyAlias.Table2ID

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

surya prakash SSC-Addicted Points: 455 More actions · Answer 3

Hi Matt,

I for got to mention that I am using SQL-Server 2000 version.

I can't be able to use your solutions.

Is there any alernative solutions please suggest me.

matt6288 SSCertifiable Points: 5058 More actions · Answer 4

This is probably the long way around but you can try something like this:

CREATE TABLE #temp(

[test] [int] NOT NULL,

[ID] [int] NOT NULL IDENTITY(1,1),

) ON [PRIMARY]

INSERT INTO #temp

SELECT clientorder_id

FROM table2 a

ORDER BY 1

SELECT test, id-(SELECT COUNT(*) FROM #temp b WHERE a.test > b.test) FROM #temp a

You can change the last select to be:

SELECT CONVERT(VARCHAR,test)+','+ CONVERT(VARCHAR,id-(SELECT COUNT(*) FROM #temp b WHERE a.test > b.test)) FROM #temp a

if you need the comma separation again.

surya prakash SSC-Addicted Points: 455 More actions · Answer 5

surya prakash

SSC-Addicted

Points: 455

July 27, 2009 at 1:03 pm

#1030703

Thanks so much Matt.

Seems to be working gr8