Select the MAX value

Hi,

In my table I have a column called ID that contain values such as
A1,A2,AA1,AG3...AU and I would like to select the maximum value in the values
that start with A followed by number in this case it should be "A2" but by
using :
"Select max(ID) from Register where ID like'A%'"
I will get "AU".
Also I have tried :
select max(ID) from Register where ID like'A[0-9]'

Cheers.

Re: Select the MAX value

Badis wrote:
> Hi,
>
> In my table I have a column called ID that contain values such as
> A1,A2,AA1,AG3...AU and I would like to select the maximum value in the values
> that start with A followed by number in this case it should be "A2" but by
> using :
> "Select max(ID) from Register where ID like'A%'"
> I will get "AU".
> Also I have tried :
> select max(ID) from Register where ID like'A[0-9]'
>
> Cheers.
>

As you are getting the max value from a text field, you are getting the
value with the highest sort order.

From the example data you have given, you would get the 'A2' value from
the second query, as there are no IDs with more than one digit following
'A'. If you want to handle any number of digits, you have to parse part
of the text value into a number so that you can sort numerically.

Something like:

select top 1 ID from Register where ID like 'A[0-9]%' order by
cast(substring(ID, 2, len(id) - 1) as int) desc

This will get the ID with the largest number following 'A', assuming
that if the second character is a digit, there are no more letters in
the ID.

Anyway, if you want to handle the IDs numerically, why do you have
letters in them?

--
Göran Andersson
_____
http://www.guffa.com