How do I get Maximum ID from an Id field in sql server

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi friends

Could any one please tell me how to get maximum id from sqlserver table.
I have a table in sql server with an ID field(primary key).Which contains data like

-------------
ID
---------------
ACF8
ACF10
ACF6
ACF13
ACF15
ACF9
ACF30

when ever I try to take the maximum id from this column I am getting ACF9 as maximum id instead
of ACF30.

I have tried with the below query also.
select isnull(convert(int,SUBSTRING(MAX(ID),4,1)),0) from TableFacility .

Please help me if anyone has idea about this.

Thank You.....
Happy Week end to all.... :)

Posted 31-Jul-14 23:17pm

SubhashRokzzz

Add a Solution

Comments

Estys 1-Aug-14 5:28am

What abour a key like ACF100? Is that bigger than ACF30 in your opinion?
They are all strings, so numerical comparison does not work here.

1 solution

Add a Solution

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

**Nirav Prabtani** · Accepted Answer · 2014-07-31T23:20:00

Solution 1

try this.. :)

SQL

with samp as(

 select isnull(convert(int,SUBSTRING(ID,4,1)),0)as ID from TableFacility
)
SELECT 'ACF'+convert(varchar,MAX(ID)) as MaxID FROM samp

Posted 31-Jul-14 23:20pm

Nirav Prabtani

Updated 31-Jul-14 23:24pm

v2

Comments

SubhashRokzzz 1-Aug-14 5:45am

Thank You Nirav Prabtani..... Your solution works fine....Thanks a lott.... :)

Nirav Prabtani 1-Aug-14 5:50am

Welcome subhash.. :)

Thanks7872 1-Aug-14 5:51am

Are you sure?

Nirav Prabtani 1-Aug-14 5:55am

I had not implemented it practically but it should work.. :)

Thanks7872 1-Aug-14 6:06am

And thats the reason of my comment.

Nirav Prabtani 1-Aug-14 6:09am

Rohan, we are not here to give exact solution of a question , we are here to only guide to a questioner and he got solution from it. that is enough for me.. :)

Thanks7872 1-Aug-14 6:14am

This is public forum. And you are not suppose to post something which is enough for you.

Further, if you 'guide' some one, guide it properly, If you are not sure about the solution, please don't post it. And always remember,you are always expected to give exact solution,not the guess. Its impossible that your code will give the required result. I am not sure why OP accepted it. Run it, see the result and come back again.

Nirav Prabtani 1-Aug-14 6:17am

Don't tell me to see results check first and than come to me.
You are arguing second time with me without check the code first time also you were wrong and this time also.

Thanks7872 1-Aug-14 6:21am

First of all,cool down. I commented after checking/testing your code and there is nothing wrong to point out something which is misleading.

Nirav Prabtani 1-Aug-14 6:24am

So my code is misleading..
And you can not force me to give exact solution, OP can modify it self if something is wrong there. In many solution i just give hint and OP got solution from hint so i think it is better than give exact solution.

How do I get Maximum ID from an Id field in sql server

1 solution

Solution 1

Add your solution here

Preview 0

How do I get Maximum ID from an Id field in sql server

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us