Problem solve Get help with specific problems with your technologies, process and projects.

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:


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 last published in January 2007

Dig Deeper on Oracle and SQL

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.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.