Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

nisc wwwxxx, wwwxxx0cm, should producers of software-based services, such as atms, be held liable for economic injuries suffered when their systems fail?, wwwxxx0cm, www.webdp.net, Event 9 IIS log failed to write entry, wwwxxx jeffs, Catastrophic failure Unexpected method call sequence. 0x8000ffff (-2147418113)., ksh lock a file, [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_DBC failed

Links

XODOX
Impressum

#1: Select the MAX value

Posted on 2007-05-29 07:08:01 by Badis

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.

Report this message

#2: Re: Select the MAX value

Posted on 2007-05-29 19:37:44 by Guffa

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

Report this message