Hi,

AAAA is greater than ZZZ because it has more characters in it.

My client sees it as being similar to 1111 being bigger than 999.

Matt.


>Hi Matt,
>
>How you define "greater" for the reference field? IOW, why AAAA is greater than ZZZ?
>
>>
>>Our tables have a reference field which the user can type into. The field can contain pretty much anything the user can think of e.g.
>>
>>AAA
>>AZ49
>>Glasgow/01 etc.
>>
>>However, the user can also ask the system to calculate the next reference.
>>
>>In our system, the string AAAA would be greater than ZZZ, and the reference after AAAA would be AAAB.
>>
>>Using MAX() in the above example would return ZZZ, when we would want AAAA returned.
>>
>>Is there a SQL statement which would return what we consider to be the maximum reference?
>>
>>Matt.