Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Automatically get record column content + 1

Posted on 2008-10-06
3
Medium Priority
?
225 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 1500 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

722 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