MAX ID

[avijit majumder]

I want to create id .
My code is ....
public static String getID()
{
try{
String qr="select max(id) from user where id like 'EMS%'";
Connection con....
Ststement st....
rs=st.executeQuery(qr);
while(rs.next()){
if(rs.getString(1)=="" ||rs.getString(1)==null)
{total_id="EMS0000";
return total_id;
}
else
{
id=id.substring(3,4);
total_id="EMS"+(Integer.parseInt(id)+1);
return total_id;
}
}//while
}catch(E.....){}
return total_id;
}//end function


NOTE:----
IN DATA BASE id= EMS0009,EMS0010......THEN MAX(ID) Return EMS0009.......
I want to EMS0010.......PL z HELP ME .

[Jeanne Boyarsky]

What database are you using?

I tried the following in postgreSQL and got EMS0010 returned. It's using an alphabetic sort rather than a numeric one, but that's ok if you always have the same number of digits. (leading zeros)

create table test(col varchar(50));

insert into test values ('EMS0009');
insert into test values ('EMS0010');

select max(col) from test;

[Tim McGuire]

Please put your code in a code block so it is easier to read.

I suspect you are getting the wrong ID back because of alphabetic sorting (9 is greater than 10) As Jeanne said, can you double-check to make sure
your data isn't like this:
EMS009
EMS0010

instead of:
EMS0009
EMS0010