Generating Sequence Number....

Hi,
I got stuck with a trivial issue. I have a table named T1 having column as Col1 (INT). I have another table named T2 having columns Col1(Int), COl2(Int). Following are the reacords in my table T1.

Col1
--
1
1
1
1
2
2
3
3
3
3
3
4
5
6
6
7

And I want to migrate the data from T1 to T2 where the Col1 data of T1 will get migrated to Col1 of T1 and after the migration is done the T2 should have the data like this.......

Col1 Col2
-- --
1 1
1 2
1 3
1 4
2 1
2 2
3 1
3 2
3 3
3 4
3 5
4 1
5 1
6 1
6 2
7 1

Thanks In Advance,
Rahul JhaI thought this would be a fun one to try - so here's a very poor attempt using an icky cursor... I don't think I have to explain to a DBA such as yourself why you shouldn't implement this method ;)

DECLARE @.col1 int
DECLARE @.col2 int
DECLARE @.id int
DECLARE @.i int
DECLARE @.x int
DECLARE @.myTable table (
id int identity(1,1)
, col1 int
, col2 int
)

SET NOCOUNT ON

INSERT INTO @.myTable(col1) VALUES(1)
INSERT INTO @.myTable(col1) VALUES(1)
INSERT INTO @.myTable(col1) VALUES(1)
INSERT INTO @.myTable(col1) VALUES(1)
INSERT INTO @.myTable(col1) VALUES(2)
INSERT INTO @.myTable(col1) VALUES(2)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(4)
INSERT INTO @.myTable(col1) VALUES(5)
INSERT INTO @.myTable(col1) VALUES(6)
INSERT INTO @.myTable(col1) VALUES(6)
INSERT INTO @.myTable(col1) VALUES(7)

SET @.i = 1
SET @.x = 1

DECLARE theIckyCursor cursor
FOR
SELECT id
, col1
FROM @.myTable
ORDER
BY col1 ASC

OPEN theIckyCursor

FETCH NEXT FROM theIckyCursor INTO @.id, @.col1

WHILE @.@.Fetch_Status <> -1 BEGIN
IF @.x <> @.col1 BEGIN
SET @.x = @.x + 1
SET @.i = 1
END

UPDATE @.myTable
SET col2 = @.i
WHERE id = @.id

SET @.i = @.i + 1
FETCH NEXT FROM theIckyCursor INTO @.id, @.col1
END

CLOSE theIckyCursor
DEALLOCATE theIckyCursor

SELECT col1
, col2
FROM @.myTable

SET NOCOUNT OFF

You know... If you provide us with *real* data, rather than just T1, T2 etc then a better answer could be provided.

(Basically, what you're trying to do sounds very wrong!)|||thnkx georgy. Even I was trying on the same line :-). But what I am looking forward is some process (without cursor) or just the query that can do the needfull. there is a huge data migration to be done. hence suggest me some thing that doesn't eat much of the system resource.|||a table with only one column where there are duplicate values cannot possibly have a primary key, so you cannot expect a good solution

export your data to excel, use a macro to apply the sequence numbers

otherwise, tell us your real situation, not this fantasy of only one column|||Ok, let's go back to the real world scenario.
Can you provide proper column headings etc and describe WHY you want to add a sequence number?
If you explain your justification for this design; perhaps we can suggest a better solution :)
EDIT: Sniped!|||Goergy I won't able able to give you the real world schema as it's highly confidential. Scenario is exactly teh same as i said. the only difference is that there are more columns than I said in both the tables.

Why I need this, is because of i am migrating the data from and de-normalised database to a normalised one.

Thanks,
Rahul Jha|||can you use an ORDER BY to get the rows into the necessary sequence for numbering them?|||select col1,row_number()over(partition by col1 order by col1)col2 into t2 from t1|||row_number?

Can you make it more clear?

Thanks,
Rahul Jha|||row_number() (http://www.databasejournal.com/features/mssql/article.php/3572301)|||pdreyer's solution actually works. I have learned something new today :)|||IVON, can you paste the code for the same. I am not getting the meaning of row_number(). How to use this function.

I'll be obliged if any one let me know on the query that pdreyer has written.

Thanks,
Rahul Jha|||I googled for ROW_NUMBER() and it seems that this is in SS 2005. This is not the part of SS 2000. And my DB is in SS 2000.

Thanks,
Rahul Jha|||please answer the question in post #7|||This one ought to leave a mark!SELECT 1 AS ick
INTO #junque
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 6
UNION ALL SELECT 7

SELECT ick, Identity(INT, 1, 1) AS bar, 0 AS bat
INTO #foo
FROM #junque

UPDATE #foo
SET bat = (SELECT Count(*)
FROM #foo AS baz
WHERE baz.ick = #foo.ick
AND baz.bar <= #foo.bar)

SELECT ick, bat
FROM #foo

DROP TABLE #foo
DROP TABLE #junque-PatP|||ORDER BY on COL1 can be done......... Let me know if i hv cleared ur doubt or not............|||have you seen pats post on page 1|||This one ought to leave a mark!ouch!

[applies mark remover]|||I googled for ROW_NUMBER() and it seems that this is in SS 2005. This is not the part of SS 2000.
True, true.
And my DB is in SS 2000.
We didn't know that.|||ORDER BY on COL1 can be doneexcellent

here is your solution:

select ( select count(*) + 1
from daTable
where Col1 < T.Col1 ) as rownum
, Col1
from daTable as T
order
by Col1

rownum Col1
1 1
1 1
1 1
1 1
5 2
5 2
7 3
7 3
7 3
7 3
7 3
12 4
13 5
14 6
14 6
16 7
here is pdreyer's ROW_NUMBER() solution for comparison --

select row_number() over
( partition by Col1
order by Col1 ) as rownum
, Col1
from daTable
order
by Col1

rownum Col1
1 1
2 1
3 1
4 1
1 2
2 2
1 3
2 3
3 3
4 3
5 3
1 4
1 5
1 6
2 6
1 7
if you remove the PARTITION BY clause, then the result is

rownum Col1
1 1
2 1
3 1
4 1
5 2
6 2
7 3
8 3
9 3
10 3
11 3
12 4
13 5
14 6
15 6
16 7|||You guys rock............

Thanks for the solution.......... Thanks patty, r937 and pdreyer......... gr8 help that was.

Thanks,
Rahul Jha|||SELECT ick, Identity(INT, 1, 1) AS bar, 0 AS bat
INTO #foo
FROM #junqueI had no idea you could insert identity values that way.|||Thanks patty

*laughs out loud*|||:) :) Don Laugh Georgy............... :) :)

I really learnt few things thru this thread.........

Thanks guys once again...... You all are awesome......... Nothing to fear Rahul till the time you guys are there......... :) :) :)

Thanks,
Rahul Jha|||I had no idea you could insert identity values that way.Identity function - I think it is new to 2005. The devs here love it because they love select into too.|||that's a negative poots. the identity function is in SQL 2k. I have used it before.|||My mistake. I came across it about the time we migrated so probably got my wires crossed there.|||well, be careful for what you wish for

http://weblogs.sqlteam.com/brettk/archive/2004/06/09/1530.aspx|||-- Prepare sample data
CREATE TABLE #Sample
(
Col1 INT,
Col2 INT
)

INSERT #Sample
(
Col1
)
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 6 UNION ALL
SELECT 7

-- Check precondition
SELECT * FROM #Sample

-- Setup staging mechanism
CREATE CLUSTERED INDEX IX_Sample ON #Sample (Col1)

DECLARE @.Index INT,
@.Col INT

SELECT TOP 1 @.Index = 0,
@.Col = Col1
FROM #Sample
ORDER BY Col1

UPDATE #Sample
SET @.Index = Col2 = CASE WHEN Col1 = @.Col THEN @.Index + 1 ELSE 1 END,
@.Col = Col1

DROP INDEX #Sample.IX_Sample

-- Check postcondition
SELECT * FROM #Sample

-- Clean up
DROP TABLE #Sample

Generating Sequence Number....

Hello folks,
I have a table where the records are like followings.

ID Value
-----
1 aa
1 aa
1 aa
1 bb
1 bb
1 bb
1 bb
1 cc
2 pp
2 dd
2 dd
3 qq
4 aa

I need to include one column "SeqId" which will be having value based on the 1st 2 columns (ID + Value). I am struglling with this since last few days. Can I request you guys to help me out in this reagard.

ID Value SeqId
----------
1 aa 1
1 aa 2
1 aa 3
1 bb 1
1 bb 2
1 bb 3
1 bb 4
1 cc 1
2 pp 1
2 dd 1
2 dd 2
3 qq 1
4 aa 1

Thanks,
Rahul JhaI cannot remember if you need to declare a length for a varchar column when used in a convert expression. Well, you'll either have 1 character or the correct number, so an easy test.

Your question about the actual expression appears a little too easy, which makes me feel I might have misread it. From what I understand it is that you need to accomplish, here is the basic idea behind a solution:

select
(convert(nvarchar, ID) + ' ' + Value) as 'NewSeq'

Of course you will need to modify the formatting to meet your exact requirement, but it does nonetheless appear to be little more than a straight forward concatenation of two fields.

Regards,|||I wish I could have done that Robert... But he problem is that the SeqId columnis of INT datatype. Hence can't concatenete.


Thanks,
Rahul Jha|||I wish I could have done that Robert... But he problem is that the SeqId columnis of INT datatype. Hence can't concatenete.

Thanks,
Rahul Jha

If that is the case, then your question does not make sense from what you have explained. Either there is more to the the problem than has been mentioned or I've really misread it.

Your question asked how to concatenate a numeric and a non-numeric column to produce a single column. In any database, such a concatenation has the possibility to produce a non-numeric result. In your example, each and every row, will produce this kind of result.

Yet you now say that the destination column is of type INT, which makes your question impossible to answer.

Please do excuse me if I haven't understood, but the problem appears to be incredibly simple. Unless of course you want to store the concatenation of the numerical representation of the characters in the value column with the numeric value in the ID column. Could this be what you are trying to achieve?

My apologies, but I found it very difficult to ascertain your requirements other than what I assumed above, from your original question.

Regards,|||You are right Robert. Mistake was from my side only. Actually i should'nt have written (ID + Value). Anyways, what I am looking for is a new column named SeqId (INT) that will contain the data as per the column ID and Value. That means for a particular combination of ID & Value the value will be 1 and then for the same ID but different set of Value the data will be 2 and then for the next set of value for the same ID it will be 3 and so on........ And the same process will start for the next ID......

Thanks,
Rahul Jha|||No, you did not make a mistake at all. I just did not read the question completely, specifically the sample output that you provided. I therefore apologise for my above comments, they are not really relevant.|||You will need access to a temporary row identifier of some description so that you can distinguish between rows that have identical values in each of the columns that are to be used to determine the value for the SeqID column.

A common approach to these types of problems in Oracle is to use the Rownum function in conjunction with a defined ordering of the set. If you are using SQL Server 2005, you can use the new ROW_NUMBER function to achieve this purpose. If you're using SQL Server 2000, the easiest approach would be to temporarily insert an identity column named rowID or similar, and of course ensure that the values within are unique.

Once you have access to a rowID using one of the above methods, the SELECT query becomes a trivial self-join. The following code shows how to produce the result set that you described, using Oracle and the rownum function.

SQL> select * from myTable;

ID VALUE
---- --
1 aa
1 aa
1 aa
1 bb
1 bb
1 bb
1 bb
1 cc
2 pp
2 dd
2 dd
3 qq
4 aa

13 rows selected.

select
t1.id,
t1.Value,
count(t2.rn) SeqID
from
(
select
id,
value,
rownum as rn
from
(select id, value from myTable order by id, value)
) t1

inner join
(
select
id,
value,
rownum as rn
from (select id, value from myTable order by id, value)
) t2
on t2.rn <= t1.rn
and t1.id = t2.id
and t1.value = t2.value

group by
t1.id,
t1.value,
t1.rn
order by
t1.id,
t1.value,
t1.rn;

This query will produce the following result set.

ID VALUE SEQID
---- -- ----
1 aa 1
1 aa 2
1 aa 3
1 bb 1
1 bb 2
1 bb 3
1 bb 4
1 cc 1
2 dd 1
2 dd 2
2 pp 1
3 qq 1
4 aa 1

13 rows selected.

I do know that you can use a multiple table select query as the criteria for an update query, but I can't at this moment remember the exact syntax. However the Select query is what's important, and what I've supplied above will produce the set that you need.

Regards,|||ID VALUE SEQID
---- -- ----
1 aa 1
1 aa 2
1 aa 3
1 bb 1
1 bb 2
1 bb 3
1 bb 4
1 cc 1
2 dd 1
2 dd 2
2 pp 1
3 qq 1
4 aa 1

13 rows selected.

This is not the resultset that i am looking for...... I am looking for something like this.......

ID VALUE SEQID
---- -- ----
1 aa 1
1 aa 1
1 aa 1
1 bb 2
1 bb 2
1 bb 2
1 bb 2
1 cc 3
2 dd 1
2 dd 1
2 pp 2
3 qq 1
4 aa 1

I am again sorry for my putting across a wrong question...... I got to know this only when you gave me the answer......... Sorry again guys.......

Thanks,
Rahul Jha|||No problem. Nothing gets me more excited than a continuously changing business requirement. ;)

One moment whilst I excuse myself from talking to my wife , to rewrite the SQL. However, could you just confirm that you are now positive that what you supplied above is the result set that you want?|||:) :) :)

I am 100% sure of my requirement this time.

Thanks,
Rahul Jha|||Also Rahul - what version of SQL Server? This is pretty trivial in 2005 using the OVER clause and RANK()|||You can use a correlated sub-query if you'd rather, which is basically what the over () would provide in this instance.

Whatever solution you're most comfortable with I guess.|||My DB is SQL SERVER 2K (Unfortunetly)

Thanks,
Rahul Jha|||You can use a correlated sub-query if you'd rather, which is basically what the over () would provide in this instance.

Whatever solution you're most comfortable with I guess.Although OVER is more efficient. Not in 2k of course... unless you count not running at all as efficient.|||Actually it was that very approach that allowed me to save my previous firm millions of pounds in manual labour costs.

"Rob, we need to improve the efficiency of this process. Could you please take a look"

"Ok, I've concluded the best approach to take is to just delete the process. In other words if we do not run it, the execution time will be zero."

"Oh good heavens, why didn't I think of that. This is what we need in this department, people who can think outside of the box."

"Exactly! Now that will be 250 pounds please."|||Although OVER is more efficient. Not in 2k of course... unless you count not running at all as efficient.
It is, check the IO and TIME statistics! ;)|||George,

I don't believe that comment is logically valid.

To

Although OVER is more efficient..

You replied with

It is, check the IO and TIME statistics!

You appear to be agreeing with the previous post. Unless it's an earth shattering improvement, should we really worry to check the IO and Time statistics.

Alternatively,

Not in 2k of course

You replied with

It is, check the IO and TIME statistics!

I think somebody forgot to read the rest of the sentence while remembering the contents of an earlier post, which stated that the OVER() analytical function is not available in 2000 and hence the running time would be zero ;)

Regards,|||Also Rahul - what version of SQL Server? This is pretty trivial in 2005 using the OVER clause and RANK()

Just to clarify, the original poster did make a significant change to the initial requirements, subsequently serving to trivialize the problem, between the first example given and the most recent example that was supplied before you made reference to the analytical functions in SQL Server 2005. ;)

Regards,|||We know Rahul well - par for the course ;)

At least both requirements are equally trivial using OVER().|||We know Rahul well - par for the course ;)

At least both requirements are equally trivial using OVER().

I would actually say that the first requirement is a little more involved than the second, for the reason that for the first requirement the poster asked that the SeqID column contain sequentially incrementing values for all rows, including those that contained identical values for each of their column values.

In contrast, the second requirement relaxed this point. The values for the SeqID column can be non-unique, and hence the comparison can be applied directly to the two static columns of the set.

Regards,|||I should have shortened the quote to

unless you count not running at all as efficient.

To make my repsonse make more sense :p

It is, check the IO and TIME statistics! ;)|||Touch

Indeed.|||I'm pretty sure it is trivial. I love this little addition to the SQL syntax (although full support in SS would be nice).

Row_number () OVER (partition by col_1, col_2 order by col_2)|||Anyway - is Rahul still waiting for his solution or did we (... Robert) sort it for him?|||Does anybody have any sweetener?

I didn't think SQL Server 2005 supported the Row_Number function as an analytical function that could be used in the way you've described in your post.

Had it not been available then... you can see my point.|||Anyway - is Rahul still waiting for his solution or did we (... Robert) sort it for him?

I'm not sure. I actually ceased to continue in actually solving the problem after the requirements changed again. Though had I known about the use or Row_Number with the OVER() clause then I most probably would have completed the solution.|||I am interested not as much with the potential solution to the OP (no offense, as you guys make it seem more exciting than being naked in a sack of kittens!), as I am with the requirement that Rahul is faced with in order to need to make the database change he seeks to make.

Often those project requirements are a great source of amus...errr...interest in his posts, though often we are not given visibility to them until the third or fourth requirement change undergone in one of these threads.|||Like the changing requirements in these threads is any different from the way things work in the "real world".

Oh, and no offense, Cowboy, but I hope I never see you quite that excited ;-).|||Like the changing requirements in these threads is any different from the way things work in the "real world".and the real world mirrors our little microcosm by bitching about them same as us :)

Oh, and no offense, Cowboy, but I hope I never see you quite that excited ;-).are you sure? I have a video on UTube! :)|||Guys where is the solution / suggestion............?

Thanks,
Rahul Jha|||I already supplied the answer in a post reply on the first page. You just need to modify the ordering condition, which is the only true variable in these kinds of problems.

Unfortunately my '(' key isn't working, so I'm unable to provide the answer.

Regards,|||Guys where is the solution / suggestion............?

Thanks,
Rahul Jha

Rahul,

If you you a tone of voice that is so demanding, would you not agree that people may be a little less inclined to offer assistance?

P.S. See my above post for the answer.

Regards,|||I am sorry Robert If I sounded rude in my sentence..... It was'nt intentional.....

Thanks,
Rahul Jha|||No problem. I understand your intentions, but other people may not.

So the problem has been resolved. That's good news.

Regards,|||But Robert I am unable to get the solution from what you have suggested in the 1st page.... What you have suggested was for the requirement that I have mentioned during the begining of the thread.

Thanks,
Rahul Jha|||Rahul,

Please see my earlier post, an extract of which is provided below.

I already supplied the answer in a post reply on the first page. You just need to modify the ordering condition, which is the only true variable in these kinds of problems.

Regards,|||An admirable effort, r123456...

one can lead a horse to water...|||I am extremely sorry Robert. But am unable to figure out the solution (Required Query).

Thanks,
Rahul Jha|||I am extremely sorry Robert. But am unable to figure out the solution (Required Query).

Thanks,
Rahul Jha

Apologies Rahul, but this thread is now closed following two days of inactivity from the original poster, which gave sufficient reason to believe that the question had been resolved.

Regards,|||I was held up at some other frontier...... And in the mean time, gave a try to resolve this according to your suggestion..... That was the reason couldn't reply to the thread. And there was saturday and sunday between...

Thanks,
Rahul Jha|||OK, you've defeated me there with the point about Saturday and Sunday. Could you please post your modified version of the query I supplied earlier that you are using?|||ALTER TABLE TempTable ADD INDENTITY_COUNTER INT IDENTITY
GO

UPDATE TempTable
SET
SEQID = (
SELECT COUNT(*) FROM TempTable TT
WHERE
TT.ID = TempTable.ID AND
TT.VALUE = TempTable.VALUE AND
TT.INDENTITY_COUNTER <= TempTable.INDENTITY_COUNTER
)

ALTER TABLE TempTable DROP COLUMN INDENTITY_COUNTER
GO

Thanks,
Rahul Jha|||I don't see why you need to create the IDENTITY_COUNTER column, the values for the SeqID column can be generated from the existing two columns that you have.

To correct your query you will need to make the following amendments to the query. Firstly you will need to remove the identity_counter column and indeed references to it from your query. Secondly you will need to modify your condition expressions to include the less than or greater than operator for one of your columns, and thirdly, the distinct keyword will need to be used with the count expression.|||Robert, I need the INDENTITY_COUNTER column to get the value for the SeqId column. That is the backbone of the query...... Even you have suggested the same in your query via ROWNUM keyword.

Thanks,
Rahul Jha|||No Rahul, that is not what I said. I indeed did make reference to the IDENTITY_COUNTER column as being one option to use in writing a query to meet your initial requirements, in the absence of the availability of SQL Server 2005's analytical functions. However if you will recall, you later changed your requirements such that the values for the SeqID column needn't be unique. This slight change removes the need for the identity_column. The requirement is now to populate the SeqID column with the count of distinct rows that have a value for the VALUE column which is less than or equal to the corresponding value of the current row.

Therefore to correct your query you will need to change one of your conditional operators to check for less than or equal to, and modify your count function to return only the count of distinct records as determined by the columns mentioned above.

Regards,|||Will it be in the lines of this........?

UPDATE TempTable
SET
SEQID = (
SELECT COUNT(DISTINCT ID, VALUE) FROM TempTable
WHERE
[SOMECONDITION]
)
GO

Thanks,
Rahul Jha|||Be Back From Lunch.... :-)

Thanks,
Rahul Jha|||Oh, I give up!|||Did I say anything which I was not suppose to.....?

Thanks,
Rahul Jha|||No, not at all. The point is that you haven't really said enough for me to be able to help. I and other posters have supplied a comprehensive explanation of how to develop a solution. You just need to carefully read the posts and amend your current query to address the considerations that have been mentioned to reflect the change in your requirements.|||I am sorry again Robert. Couldn't able to figure out the solution. But anyways, thanks to you guys for the kind help......

Thanks,
Rahul Jha

Generate Sequence Number based on other columns

Hi,
Please help me to find a solution.

My requirement is similar to this.
Example:

TABLEA(pk_id, pol_mas_id,pol_seq_id)

values are
pk_id pol_mas_id pol_seq_id
1 1
2 1
3 2
4 2
5 3
6 3
7 3

Now i need to update the column "pol_seq_id" as below.

pk_id pol_mas_id pol_seq_id
1 1 1
2 1 2
3 2 1
4 2 2
5 3 1
6 3 2
7 3 3

Currently i am using cursor and you know performance sucks.

Is there any way to increment the data in the column(pol_seq_id) based on other column (pol_mas_id).

Thanks in Advance!!

Try:

select

pk_id, pol_mas_id, row_number() over(partition by pol_mas_id order by pk_id) as pol_seq_id

from

dbo.t1

go

It is helpful to have an index by (pol_mas_id, pk_id)

AMB

|||Thanks a lot for the response !!!
It works for select as when i am trying to update the same i am getting this error
"Windowed functions can only appear in the SELECT or ORDER BY clauses."
so i have populated the same into #temp table and then updated with join statement.

It works
|||

Try using a CTE.

;with cte

as

(

select

pk_id, pol_mas_id, pol_seq_id, row_number() over(partition by pol_mas_id order by pk_id) as rn

from

dbo.t1

go

)

update cte

set pol_seq_id = rn;

AMB