Max function

[sigh] You are getting this because it is returning the max ASCII value.
If you do an "order by" the same thing will happen.

You will need to parse the field selecting everything after the second '-'
and then converting it to a number. This is database specific so which database
are you using? If it is SQL Server, Dave will gladly (and correctly) answer
the question. :)

Mark

"Nicole" <jason_sta_catalina@hotmail.com> wrote:
>
>I have a field "ActivityCode" data type is string.
>content example "1-1-1".
>
>I select the max activitycode using SQL MAX function so
>I can increment it like .. "1-1-2"
>
>Everything works fine until the Activitycode reaches "1-1-10"
>
>My select max function would return "1-1-9" even there is "1-1-10"
>
>Are there any function in SQL that would solve my problem or
>any suggestion or workarounds?
>
>Thanks. Have a nice day! :)
>

Some good suggestions. I tire of climbing on my soapbox so I didn't go there.
But since you did ... .

Only thing I would suggest is not using a SP. If application is coded properly,
then there will be only one place to change in the code too. And with the
proper tools, the users will not need to stop using the db. Most SP changes
in most databases require dropping the SP and re-adding it with the changes.
Plus other issues.

"Burt" <brodman@vulcraft-in.com> wrote:
>
>If this db is in development I would suggest normalizing this into multiple
>fields. If not, then parsing the field is probably the easiest way.
>However, even if your db is in production, if this is not a Key field you
>may be able to get away with nominal changes in your code, and even better
>if you're accessing your database through sp's you may get away only changing
>the sp -- handling all the parsing or combining inside the sp.
>
>
>"Nicole" <jason_sta_catalina@hotmail.com> wrote:
>>
>>I have a field "ActivityCode" data type is string.
>>content example "1-1-1".
>>
>>I select the max activitycode using SQL MAX function so
>>I can increment it like .. "1-1-2"
>>
>>Everything works fine until the Activitycode reaches "1-1-10"
>>
>>My select max function would return "1-1-9" even there is "1-1-10"
>>
>>Are there any function in SQL that would solve my problem or
>>any suggestion or workarounds?
>>
>>Thanks. Have a nice day! :)
>>
>