how can I get maximum values from set of alphanemeric field [modified]

[snehasish]

Hi; I create a field(namely Id) which have two types(09a.. or 09b..) of alphanumeric value like; Id 09a1001 09a1002 09b1001 09b1002 09b1003 from these numbers at time a i need maximum value of 09a (it will be 09a1002) series or maximim value of 09b series(it will be 09b1004). Help me.

modified on Thursday, January 29, 2009 7:57 AM

[Wendelius]

You could have something like:

select max(convert(int, substring(id, 4, 9999)))
from tablename
where id like '09a%'

The need to optimize rises from a bad design.My articles[^]