Max Function Problem

1. Join Date
Dec 1969
Posts
85

## Max Function Problem

I have 11 rows with 1 through 11 stored in a varchar datatype. I am pulling the information back and trying to get the max(column) which should return 11, but it is returning 9. When I order the column ascending, it orders it 1,10,11,2,3, 4, etc... <BR><BR>Anyone have a clue why that is happening?

2. Senior Member
Join Date
Dec 1969
Posts
2,243

## RE: Max Function Problem

Because it is a varchar!

3. Join Date
Dec 1969
Posts
85

## RE: Cast the value as int

After I posted it, I thought about casting the column to an INT and guess what, it works. Not sure why, but it now works. If anyone knows why it doesn&#039;t work as a varchar I still would be interested to know why.

4. Senior Member
Join Date
Dec 1969
Posts
96,118

Okay, you have this information stored as VARCHAR.<BR><BR>Which means that, when you compare it (which is what happens in an ORDER BY) you are doing a *string* comparison.<BR><BR>Now...<BR><BR>If the data were<BR> 1A<BR> 1B <BR> 1C<BR> 1D<BR> 1AA<BR> 1AB<BR>would you be surprised to find that ORDER BY that field produced<BR> 1A<BR> 1AA<BR> 1AB<BR> 1B <BR> 1C<BR> 1D<BR><BR>Of course not. That&#039;s normal alphabetical ordering, right?<BR><BR>So why are you surprised to see the order<BR> 1<BR> 10<BR> 11<BR> 2<BR> 3<BR>given the data that you have? WHEN TREATED AS STRINGS, digits have NO special meaning and you will NOT get a numerical ordering.<BR><BR>Similarly, to calculate the MAX of that field, the DB must do a STRING comparison, so *anything* starting with &#039;9&#039; will be "higher" than anything starting with any other digit.<BR><BR>So the first question: WHY are you storing NUMBERS in a VARCHAR field??? If you never store anything but numbers in that field, then it should be a numeric field type, most likely INTEGER.<BR><BR>If you *do* have some non-numeric values mixed in, then how do you want to handle them??? Omit them entirely? Put them after all the numeric values? Ahead of all the numeric values?<BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
96,118

## If you *can* cast it as INT...

...then the field should never have been VARCHAR in the first place.<BR><BR>See my other answer, below.<BR>

6. Join Date
Dec 1969
Posts
85

I do have non-numeric values that are stored in the same column, but I know when it is supposed to be a numeric and when it will be a non-numeric number. It is a very broad database design and allows for great expandability. It will be a very lengthy discussion if we get into the database build. <BR><BR>Thanks for the information

7. Senior Member
Join Date
Dec 1969
Posts
96,118

## You can protect yourself...

...from getting non-numeric values by using the ISNUMERIC function.<BR><BR>ORDER BY ( CASE WHEN IsNumeric(field) THEN CONVERT(INT, field) ELSE 999999 END )<BR><BR>Or something similar.<BR><BR>But if you are indeed prefiltering to get only the numeric fields, then the simple CAST or CONVERT is adequate.<BR><BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•