Retrieving max value from a varchar field

Question

Retrieving max value from a varchar field

itrap

SSC Enthusiast

Points: 125
More actions
March 29, 2004 at 9:14 pm

#107470

Hello,
I have ID field in a table which assigns unique ID to each new record inserted in the table. IDs have a form M###### where M is a character and ###### is a number which increments every time new record is added.
for instance, this table would have records having IDs M000001, M000002, ..... M000010 ... etc..
Any ideas how can I assign such ID automatically, or is there a way to get record last inserted.
Thanks

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

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Personally I would use an identity field to do the incremementing. The value can bet cast to a varchar and preceeding 0's and M can be concatinated on for looks. Is there a reason specific why that would not work for you?

itrap SSC Enthusiast Points: 125 More actions · Answer 2

I am not an experienced SQL Server programmer ... not sure how this can be done

I would appreciate if someone can give me ideas as to how this can be achieved.

SmallYellow SSC Enthusiast Points: 119 More actions · Answer 3

Your best bet is to use a seperate identity column to increment.

You can alter your table as follows:

alter table

my_table

add

my_idcol numeric identity(1,1)

go

If you apply this to the table it will add the column and back fill it with values from the start of the table. You can change the first digit after IDENTITY to allow the increments to start from a number other than 1. Changing the second number will allow you to change the increment steps - 1,2,3,... and so on.

As Antares said, you can concatenate on the 'M' and the zeros at any point after that - when you retrieve the values from the table - unless you have a specific reason to store it in this format.

Thanx, I'm here all Week,

SmallYellow

itrap SSC Enthusiast Points: 125 More actions · Answer 4

Thanks guys for your help,

Here is my solution to the problem. Instead of creating another column just for storing ids I am using count() function in select statement to get number of records in the table ..

Rest is simple I have to just concatenate this value with M and 0s

MattieNH SSCertifiable Points: 7463 More actions · Answer 5

If you delete a record from the table, unless it's the last one, won't a count give you a duplicate?

ie: Records 1 to 10, #7 deleted, so now you have 9 records, meaning count + 1 will be 10, which you already have.

Mattie

itrap SSC Enthusiast Points: 125 More actions · Answer 6

Oooo woww ..... I didnot think about that, that sure would create duplicate entries. Anyway good thing is right now the system is designed is such a way that no entry in the table can be deleted so that problem wont arise.

But at a later stage I do plan to move some of the records from this table to a back up table so that queries can run faster. Thanks for bringing this to my notice.

mathurar Old Hand Points: 358 More actions · Answer 7

The count(*) will always give unique id's but the row data will have a dynamic id and not a static id. For a given row the id will be changing depending on the sequence in which sqlserver retrieves data.

If you have the scope to add a column, continuing on SmallYellow's idea add the identity column and convert the existing "id" column to a computed column as

alter table my_table alter column id as 'M'+right('0000000000'+cast(my_idcol as varchar(20)),10)

Hope this helps.

AM

itrap SSC Enthusiast Points: 125 More actions · Answer 8

Thanks mathurar .... 'myidcol' was exactly what I was looking for .... since I didnt find tat on time I decided to look for other means of getting an unique identifier

Thanks again for the help