How to do a query ;select max(id)+1

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

SQL-Server

Hi,

please How to do:

Select (MAX(ID)+1) from table;

thanks in advance.

Posted 22-Jan-12 9:32am

m_safaaaa

Add a Solution

4 solutions

Solution 3

SQL

SELECT MAX(COLUMNNAME) + 1 AS Result FROM TableName

The datatype of column should be numeric/int to product exact result

Posted 22-Jan-12 19:29pm

koolprasad2003

Comments

Member 12968503 31-Mar-17 5:13am

what if its NULL

koolprasad2003 3-Apr-17 0:49am

As I said, 'The datatype of column should be numeric/int to product exact result' if it has NULL value, it return MAX as 0 and add 1 in it.
So there is no issue if it has NULL value

Solution 1

hi,

try to check following link:
http://msdn.microsoft.com/en-us/library/ms187751.aspx[^]

here you can find explanation of Max TSQL function. But can you tell me what do you want to do exactly?

Regards
Robert

Posted 22-Jan-12 9:54am

Kanasz Robert

Comments

m_safaaaa 22-Jan-12 23:45pm

hi, thank you for your answer. what i want to do is:
wrinting one sql query where i select a value incremented by 1 from one table ( in my case the value is equal to MAX(IdProject) ; IdProject is a column in the table PROJECT)

Best Regards

Safa

palraj001 22-Jan-12 23:56pm

what data type u have used for IdProject?

m_safaaaa 23-Jan-12 0:12am

int

palraj001 23-Jan-12 0:27am

then it should increment the value.

check with select Max(IdProject), max(IdProject) + 1 from PROJECT

Solution 4

hi dear friend ,

please try this query

<u>
SELECT MAX(isnull(COLUMNNAME,0)) + 1 AS Result FROM TableName</u>

Posted 30-Jan-12 23:51pm

vijaynet10

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Amir Mahfoozi · Accepted Answer · 2012-01-22T19:06:00

If you have multiple client access at the same time you will have race conditions and you will produce duplicate next IDs in that case.

Start a serializable transaction and then compute and use the largest number to avoid duplication.

Read the answer on this page to get the idea :
How to autoincrement in SQL[^]

For more information about transactions :
http://msdn.microsoft.com/en-us/library/ms188929.aspx[^]

http://msdn.microsoft.com/en-us/library/ms173763.aspx[^]

http://msdn.microsoft.com/en-us/library/2k2hy99x%28v=vs.71%29.aspx[^]

Hope it helps.