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