Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Apr 2006
    Posts
    9

    Unanswered: MAX() not returning max value

    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:

    Code:
    SELECT max(testField) FROM table1 WHERE testField like "text%";
    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2006
    Posts
    9
    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:

    Code:
    select concat('featuredCars',0+max(substring(portion from 13))) as daMax from `content` where `page` = 'cars' and `cat` = 'featuredCars'
    Omitted the like part, didn't change anything.

    I have:
    featuredcars1
    featuredcars2
    featuredcars3
    featuredcars10

    featuredcars3 is getting returned

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --

    select concat('featuredCars',max(0+substring(portion from 13))) as daMax
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2006
    Posts
    9
    That did it thanks man

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Apr 2006
    Posts
    9
    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

  8. #8
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    What about some normalisation here???
    The table should be called featured cars? And it should have incrementing ID (integer) ?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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)

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by r937
    any time i see it misused, i will always attempt to refute or correct
    I'll vouch for that
    George
    Home | Blog

  14. #14
    Join Date
    Apr 2006
    Posts
    9
    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

  15. #15
    Join Date
    Apr 2006
    Posts
    9
    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.

Posting Permissions

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