Find maximum value from any table(It's very urgent)

Question

5.00/5 (1 vote)

See more:

SQL-Server-2005

Hi,
Please help me to short out this problem while executing this query.

I have a table with fields like id,name,password. I want to find the maximum value ie. id i have following values enterd.

SQL

ID	Name	Password
1	abcd	111111
2	wer	222222
3	dsf	1234
4	fgty	5555
5	ghyu	77777
6	hhnj	666666
7	gggbv	99999
8	eert	8888888
9	fvgb	0000000
10	hjuik	3232323
11	erwq	45455
12	sdfgh	4565656
13	wewe	4545656
14	sdcvf	65656
15	wwwer	565656
16	fgbgh	6566
17	ertyu	6566

When executing this query ie. select distinct max(id) as ids from user.
But i am not getting the exact result.

I GOT THE RESULT LIKE ID IS 9 BUT THE ACTUAL RESULT IS 17. HOW CAN I FOUND THE VALUE 17?

Posted 20-Dec-11 19:13pm

Rajiv nayan

Updated 20-Dec-11 19:15pm

Wendelius

v2

Add a Solution

5 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)

Wendelius · Accepted Answer · 2011-12-20T19:20:00

Solution 1

You don't have to define DISTINCT for the query. Simply:

SQL

SELECT MAX(Id) FROM User

However, it sounds that you're trying to get the maximum in order to define the next key value by yourself. If that's true, don't do that! Instead use IDENTITY in CREATE TABLE[^].

Also based on your results, make sure that the data type for id is numeric (for example (int) not varchar.

Posted 20-Dec-11 19:20pm

Wendelius

Comments

Amir Mahfoozi 21-Dec-11 1:40am

+5

Wendelius 21-Dec-11 3:51am

Thanks :)

Rajiv nayan 21-Dec-11 1:54am

thanks
i made id column as varchar that's why it will not give the accurate result, when i will change it as integer then result is accurate.

Wendelius 21-Dec-11 3:50am

You're welcome, always try to use appropriate data type for a column :)

**Orcun Iyigun** · Accepted Answer · 2011-12-20T19:22:00

Solution 2

The MAX() function is not recommended because you might get some other identity value that is not yours but from a different user :) I think thats what happened to you. If you are only looking for the highest identity value you can use:

instead use @@identity

Here are some links:
@@IDENTITY (Transact-SQL)[^]
SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record[^]

Good luck,
OI

Posted 20-Dec-11 19:22pm

Orcun Iyigun

Comments

Rajiv nayan 21-Dec-11 1:44am

thanks
i made id column as varchar that's why it will not give the accurate result, when i will change it as integer then result is accurate.

Wendelius 21-Dec-11 3:51am

Sorry for the first comment, mistakenly added to wrong place.

Exactly, using identities is a much better solution. 5'd

Orcun Iyigun 21-Dec-11 12:51pm

Thank you Mika. No worries about the first comment :)

koolprasad2003 · Accepted Answer · 2011-12-20T19:34:00

Solution 4

it looks your ID column is not interger it's a varchar and your try to find MAX from varchar.
you need to change datatype of column to int.

e.g.
if i have data p1,p2,p3,p4,p5....p9,p10. and i put MAX then result will be p9 not p10 cause
it consider p10 as 'p','1','0' as an individual characters.

Posted 20-Dec-11 19:34pm

koolprasad2003

Comments

Amir Mahfoozi 21-Dec-11 1:40am

+5

MaulikDusara · Accepted Answer · 2011-12-20T19:30:00

Solution 3

Hi,
Try below sql

SQL

SELECT CONVERT(INT,[ID]) AS IDS FROM USER

Posted 20-Dec-11 19:30pm

MaulikDusara

Comments

Rajiv nayan 21-Dec-11 1:54am

thanks
i made id column as varchar that's why it will not give the accurate result, when i will change it as integer then result is accurate.

Amir Mahfoozi · Accepted Answer · 2011-12-20T19:37:00

Solution 5

It is very clear that in case of having a varchar ID ! we will have 9 greater than 17 !

So either change your ID type to an int datatype or use this query to change it on the fly

SQL

select max(CAST(id as int)) from BadDesignedTable

Hope it helps.

Posted 20-Dec-11 19:37pm

Amir Mahfoozi

Comments

Rajiv nayan 21-Dec-11 1:53am

Thanks Sir It's work very fine.

Amir Mahfoozi 21-Dec-11 2:04am

You're welcome :)