Finding The Highest Or Largest Number In A Column Problem

Example data

CA1000

CA10001

CA10002

CA10003

CA11597

CA11603

CA1001

CA998

CA999

As you can see, CA11603 is the largest number in this list. 

 When I try the follow sql code,

SELECT

MAX([MyCode])

FROM

[MyTable]

WHERE (SUBSTRING([MyCode], 1, 2) = 'CA')

The largest number comes back as CA997

When I try

MAX([MyCode])

FROM

[MyTable]

WHERE [MyCode] LIKE 'CA%'

 The largest number comes back as CA997

 

SELECT

TOP 1 (SchoolMasterCode)

FROM

SchoolMaster

WHERE (SUBSTRING(SchoolMasterCode, 1, 2) = 'CA') ORDER BY Schoolmastercode

 The largest comes back as CA10001

When I try....

SELECT

TOP 1 (SchoolMasterCode)

FROM

SchoolMaster

WHERE (SUBSTRING(SchoolMasterCode, 1, 2) = 'CA')

 The largest comes back as CA1278

What am I doing wrong?