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.
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.
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