Find the maximum id from sql table

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

id Name
1 xxxx
2 wwww
3 uuuu
4 iiii
5 oooo
6 hhhh
7 gggg
8 nnnn
9 zzzz
10 yyyy
11 ssss
12 aaaa

my data is like that. Here I want to find the maximum id. I use the query "select max(id) from tablename. but it show 10 is maximum id. how I can find max id.

Posted 22-Jul-15 0:10am

Member 11766476

Add a Solution

3 solutions

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)

Kornfeld Eliyahu Peter · Answer 1 · 2015-07-22T00:24:00

Solution 2

Impossible...

If id defined as numeric (int) it must return 12, however if id is string (nvarchar) it must return 9 (10 comes after 1 in an alphabetic short)

To ensure that it will behave like numbers do, you must declare id as number (int)...

Posted 22-Jul-15 0:24am

Kornfeld Eliyahu Peter

Comments

_Asif_ 22-Jul-15 6:52am

+5

Kornfeld Eliyahu Peter 22-Jul-15 6:53am

Thank you...

Animesh Datta · Answer 2 · 2015-07-22T00:37:00

Solution 3

Id is not numeric thats why result comes wrong . If it is numeric then result will be 12 . so,change the datatype of Id column . or other way , you can Convert the datatype of Id column and get the Max value by this way.

select max(convert(int,id)) from tbl

Posted 22-Jul-15 0:37am

Animesh Datta

Comments

Aravindba 22-Jul-15 6:52am

5+,yes sometimes database and its table created by some other person,so better to use Convert function to convert id column as integer when u select max id.

Animesh Datta 22-Jul-15 7:15am

Thanks

Wendelius · Answer 3 · 2015-07-22T00:23:00

Solution 1

SQL

SELECT MAX(id) FROM TableName

Will return 12 if the table contains the data you posted. There are a few possibilities why you could get another result
- id's 11 and 12 are not in the table
- you have a condition in the select which eliminates those id's from the result
- id's 11 and 12 are inserted to the table in another session and the transaction isn't committed yet.

Posted 22-Jul-15 0:23am

Wendelius

Comments

Kornfeld Eliyahu Peter 22-Jul-15 6:27am

Or as in my answer - id not numeric...

Wendelius 22-Jul-15 6:55am

I wouldn't say so. If it's a string then 9 would be the max. Consider the following
select max(col1)
from (select '1' as col1 union all
select '2' as col1 union all
select '3' as col1 union all
select '4' as col1 union all
select '5' as col1 union all
select '6' as col1 union all
select '7' as col1 union all
select '8' as col1 union all
select '9' as col1 union all
select '10' as col1 union all
select '11' as col1 union all
select '12' as col1 ) a

Kornfeld Eliyahu Peter 22-Jul-15 6:56am

Exactly - that's why I wrote that 10 is impossible to get with the data in the original question!

Wendelius 22-Jul-15 7:01am

Yep, it would be nice to know if the query really returns 10 or not...

Kornfeld Eliyahu Peter 22-Jul-15 7:04am

I would not wait for OP to update...Unless you have nothing to do till eternity...

Wendelius 22-Jul-15 7:12am

:)