Solved

Automatically get record column content + 1

Posted on 2008-10-06
3
213 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 22649350
>>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 143

Accepted Solution

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sql Server group by 10 49
Parse this column 6 35
Database maintenance 36 101
Why is this SQL bringing back extra rows? (parsing XML data) 4 36
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

749 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