Q

Generating numbers with MAX(id)+1

In a Ticket table I have a field TicketID. Each time when a user wants to enter a new record, a new ID is generated. The new id becomes result+1. I want to know that the above field is a string.

In a Ticket table I have a field TicketID which I am using in a format like:

00021-10-06-0201

The first set (left side) represents Ticket No. Then month, year and code. Each time when a user wants to enter a new record, a new ID is generated as:

Select max(Left(TicketID,5)) from Ticket

The new id becomes result+1. I want to know that the above field is a string. But when I use LEFT function, as above, it successfully returns max of the first set. Does it automatically convert it to a long integer? If not, how to use it securely so that first the left set is converted to long integer and then the MAX is found? All this needs to be done in a SQL query.

There are actually several questions here. First, the MAX() aggregate function works perfectly well on strings, and of course LEFT() produces a string. Conversion to numeric format is implicit when you use the MAX() result string in an arithmetic operation in Microsoft Access (which you're probably using, since you mentioned a "Long Integer"). You don't have to worry about converting the LEFT() result to a numeric value before taking the maximum, since your strings seem to be formatted with leading zeroes, so they should collate as expected, i.e., numerically.

However, your decision to use an "intelligent" id, where portions of the id have special meaning, is questionable. My advice would be to use a regular old autonumber for the id, and have month, year and code as separate columns. This way you are spared from having to do the MAX(id)+1 trick yourself, which can lead to other problems (e.g., race conditions). You could always combine your four columns for display purposes by concatenating them in your SELECT queries.

This was first published in January 2007
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close