Query for getting maximum value

[Vikram Raman]

Hello Everyone,

I have a table named Customer with the fields CustomerId,Name,Address,PhonNo.

I have ten records there

C001......to ......C0010

When i execute the following query, it shows the wrong answer

select max(CustomerId) from Customer

CustomerId
-----------------
C009

Whats the problem with this query,

Please get me a solution for this

[code green]

When i execute the following query, it shows the wrong answer

select max(CustomerId) from Customer
C009

This is the right answer because the id is alphanumeric.
If you want to sort by numeric then the string needs parsing

[Vikram Raman]

Can you show one example please

[code green]

From memory something like

SELECT MAX(CAST(SUBSTRING(CustomerId,1,LEN(CustomerId) AS INT)
FROM Customer

So the idea is I take all the figures after the first figure,
cast this to an integer, then get the maximum value.
You may have to play about with this.
Or maybe RIGHT or LEFT maybe better.
But you need LEN to get the total length of the string