Hi all What mysql field datatype should i choose for my sorting field, if i like to use 1a, 1b, 2a, 3a, 3b, 3c etc?? Just varchar??
If that truly is one field and that's representative of the data you will be storing, then yes, varchar is correct. But I have a strong suspicion you are really dealing with 2 fields. Does the 1 mean something in and of itself? Does the letter following the number further define the number? If so, store them separately. An int field for the number and a varchar for the letter. I have had the misfortune of inheriting systems like this. There was a status code field and in it was a bunch of codes like '1A', '3C', '7A', '1Aa', '12b' etc. My job was to extract data per the user's criteria and every time the requests were similar to 'Give me all accounts with status codes beginning with 1. I did that, but I got a lot of results that they didn't want because codes like '12b', '101c' got picked up because they started with 1. Moral of the story--break out your data to the smallest possible element.
Actually now that I reread your post, I see you are using it as sorting field. I assume this means you will be using it make sure your query/report/output orders correctly. If that's true, my story still applies, but in a different manner. The below list is ordered correctly because they are strings/varchars: 1 11 11c 11d 1a 1b 2 2a 3 3a My point is, when ordering strings/varchars, 11 comes before 1a.
tx, guys @plog, tx for the explanation. In my case it's a bit lazy on my part. I got a 500+ names in a particular order. But now i need to put a names in between on certain different places. As my orderingField was just 1-500 as INT and was thinking to use 66a, 66b for example. So this would add 2 more names between 66 67. I am going to try the Varchar way.