Automatically get record column content + 1

Hello,

We are experiencing some problems when executing the next SQL query:

SELECT MAX(Code) + 1 FROM [@OLL1]

This query works fine for the first 10 results, but after the column content is 10 or higher it always returns 10; without adding the +1..

How can we get the next itterated number? Is there any other sql function to use?

AGIONAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
your field (code) is varchar and not int.
change the data type to int, and you should get better values.

NOW, if this is to generate the next code value for a INSERT, you really should make that column int (identity), so sql server will generate the value for you (multi-user-safe !!!), you will then retrieve the generated value with, for example:

SELECT SCOPE_IDENTITY()

after the insert.
0
 
chapmandewCommented:
>>This query works fine for the first 10 results
I would think that your query would only return one record

try this instead since you're on 2005


select code, row_number() over(order by code desc)
from @OLL1

0
 
AGIONAuthor Commented:
The only problem is that it's a Table that is created by a SAP application, which has strict standard. But i solved it by converting it in the query itself.
0
All Courses

From novice to tech pro — start learning today.