
Question:
What data type should i use for a SQL column to store Product version eg.
Version 0.1 0.1.1 0.2 1.1 1.1.647 2.0 .....
In query i should be able to sort them based on version number and i want an optimal query to find highest number.
Thanks
Solution:1
I would consider storing each part of the number in a separate TINYINT/SMALLINT field.
Solution:2
Perhaps have three or four numeric fields to the version table:
Major, Minor, Revision, Build
Solution:3
Storing in separate numeric fields is a good idea. Storing as a string in a single field will break sorting when one of the parts reaches 1000. For example, 1.2.999 will appear before (or shown as newer than) 1.2.1000 when it should appear after.
Solution:4
A good solution would be to use an integer building the value to store like so:
MAJOR * 10000 + MINOR * 100 + Revision
Assuming each one can range from 0..99. If you want to go 0..999 use
MAJOR * 1000000 + MINOR * 1000 + Revision
This will sort properly, will query easily, is compact (1 int column), is easily decomposed and can even be decomposed visually.
Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
EmoticonEmoticon