<h3><font color= "#408080">Problem while using <b>MAX</b> function</font></h3>

create table emp
(e_id nvarchar(50) primary key,
ename varchar(50))

e_id | ename
a1 x
a2 y
a.. n..
a15 z

select max(e_id) from emp

output :
a9

you can notice that above out put is wrong
the answer as to be a15….

Problem:
You will get an insight by running this query:
SELECT e_id from Emp ORDER BY e_id

You will see that e_id is being sorted by the character’s sequence and not like numbers. So, a10 will be followed by a1.
So, as per sort logic, a9 is the max value.

Answer:

SELECT LEFT(e_id, 1) + CAST(MAX(CAST(SUBSTRING(e_id, 2, LEN(e_id)) AS INT)) AS NVARCHAR(50)) FROM Emp GROUP BY LEFT(e_id, 1)

Please note that this query is based on the first character of the e_id column values. If let say someone inserts another 15 records starting from b1 , x to b15, z then the output of this query will be of 2 rows:
a15
b15

Warm Regards,

Siddhesh