Solved

Automatically get record column content + 1

Posted on 2008-10-06
3
204 Views
Last Modified: 2013-11-05
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?

0
Comment
Question by:AGION
3 Comments
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
>>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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:AGION
Comment Utility
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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video discusses moving either the default database or any database to a new volume.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now