Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2010

    Unanswered: Max Function Problem

    Hi everyone

    i m new in sqlserver 2005. i have crated a table with a field with varchar data type. This is the primary key of my table. the value inserted into this field is consisted of a character and number such as 'W001, W002, W0010'. now i have created a query to find the max value of the field. to do this i have used max function. but the problem is using MAX FUNCTION it can show the value until "W009". but it can not show the value of "W0010". but i need to find the max value. any one is there who can help me to find the solution.


  2. #2
    Join Date
    Mar 2009
    You are doing string comparison, which compares on a character by character basis.

    When you look at W009 and W0010, the first character is compared - W is the same as W, so moves to second, both are 0, so third character, same again. The fourth character is where the difference occurs, you have a 9 comparing with a 1, 9 is greater so W009 is higher than W0010 as far as string comparison is concerned.

    If you want to find the greatest integer portion of it then you would need to strip the W, cast the remainder as an integer and find the max there, but even then you would still have some difficulties depending on the other ranges in your table. I.e. If you had W0010, would that be the same or greater than W010 or W00010?

    I don't like tables that use character data as primary keys - you really should go for numeric data as your keys as much as possible, drop the W or move it to another field depending on what it represents. Does it specify a customer/product category? If it does then make a category field and put the W in there for example.

  3. #3
    Join Date
    Apr 2002
    Toronto, Canada
    there is nothing wrong with using character data as primary key

    however, the quest to find the MAX value in a column is a huge warning sign, because it often signals that there might be some MAX(pk)+1 business going on, which would be, shall we say, somewhat less than best practice | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2003
    Provided Answers: 1
    To be a bit more accurate, there are pros and cons to using character data as a primary key, just as there are pros and cons to any solution.

    Agree with Rudy about the "Why the hell are you doing this anyway" thing, though.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

Posting Permissions

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