Thanks very much - that looks just the ticket.
>You have to pad them from the left to the same length to make comparable. Assuming that reference cannot have leading spaces and max len of it is 30 characters
>
>
>SELECT
> LTRIM(MAX(RIGHT(REPLICATE(' ',30) + RTRIM(ref),30))) As MaxRef
>...
>
>
>>
>>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.
>>
>>
>>>>>
>>>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.