This is probably a simple lack of knowledge on part.

Ive used max() fine with staight intergers but I have a field (varchar) that has mixed value's: testN. Where N is an incrementing number.
Ive read the docs and it says that max can take a string and:

All works fine up to "text9". Once its "text10" or anything above it always returns "text9" as the max value.

Theres no indexes on the table at all yet.

that's because 9 is greater than 1

you're comparing strings, so the comparison goes byte by byte, left to right

try this --

select concat('text',0+max(substring(testField from 5))) as daMax from ...

Hey,

Yea I figured it would be something like that.

I put my "real world" values in but it doesn't seem to return the max still:

Omitted the like part, didn't change anything.

I have:
featuredcars1
featuredcars2
featuredcars3
featuredcars10

featuredcars3 is getting returned

try this --

select concat('featuredCars',max(0+substring(portion from 13))) as daMax

That did it thanks man

or better yet coerce your data so that its storage and display makes sense to both Human & machine. The
eg
featuredcars1
featuredcars2
featuredcars3
featuredcars10

becomes
featuredcars01
featuredcars02
featuredcars03
featuredcars10

...or
featuredcars 1
featuredcars 2
featuredcars 3
featuredcars10

or just ditch the "featuredcars" bit and store a numeric value. if "featuredcars" appears in all records then almost certainly its redundant.

the solution suggested by Rudy works, as you'd expect, and it works on your current dataset, but its not neccessarily the correct longterm solution for the app. placing leading spaces or zeros would make more sense. Id be minded to try ands establish what the maximum value would be and say make it one power higher (say you decided that 55 was the maximum value then Id want to use 055 as a minimum) to cater for changes outside your control

Hey thanks for that

I have in fact changed the "system" to not use the featuredCars bit. Originally I was using that as it tied in with associated file/folder structure but when this error in the SQL propped up I figured it was not the smartest way to go about it so I scraped it all for id's.
The point of the post was for me to learn why it didn't work.

Thanks for the help guys

What about some normalisation here???
The table should be called featured cars? And it should have incrementing ID (integer) ?

um, aschk, neither changing the table's name nor assigning an auto_increment is in any way "normalization" (sorry, i gots to spell it the canadian way)

in fact, assigning an auto_increment is often a step backwards

Heh, this wasn't quite what I was saying. Briefly looking at what was provided it seemed to me that what was being input was ONLY featured cars. And thus you're looking at storing featured cars, each one individual from the next, separated by their number.

Normalisation (not the bastardised AMERICANISATION `normalization` ) was probably the wrong terminology to use there, but it seems like you're really taking out a complete data set from your original table, which suspiciously looks like OTLT
Difficult to say really without a full look at the data being stored and the surrounding structure. I was hoping to shed a light shade of grey onto the situation.

minor spelling differences aside, i agree with you about looking with suspicion on this table

however, the word "normalisation" is bandied about by many people who have little regard for or perhaps even awareness of what it actually means

any time i see it misused, i will always attempt to refute or correct

Permission to refute or correct granted. You are indeed correct.
I think the three questions should have been separated.
1) Is the data normalised?
2) Have you considered using a table for "featured cars" with an ID value?

Of course it would appear he answered his own question with the removal of this information from the generic table and replacing somewhere else.

Thankfully we managed to answer his question re: 3 > 10 (in strings)

I'll vouch for that

Hey guys,

Sorry I missed all those replies.

The table was called content and was storing site wide content in a flat table:

id (auto_inc, pk)
page (page data is for IE: home, cars etc)
cat (category or better described as area IE featuredCars or newsFeatures)
portion (Individual sub category IE featuredCars1, newsFeatures4)
text (Text to be displayed)
pageorder(Integer of order on the, cms allows the reordering of the display)

I say was because I have since split (normalised, sorry I'm a kiwi ) the data up into separate tables with relationships etc etc

Off topic for this thread really but since there was interest re the structure, heres a visio (good grief!) erd:

http://i17.photobucket.com/albums/b6...tal_new_db.jpg

Theres a user and config table which aren't shown, I dont link them to anything..
I realise I could probably combine the comingUp and newsFeatures table, may do in fact.