7.1 < 7.10 - ordering numbers

Question

Is there a way to store numbers in a db so that 7.1 is less than 7.10, ie keep the 2 points? Currently the numbers are stored as floats - do I need to change to small money, decimal or something? Any help hints appreciated.

Currently the .Net object uses a Double and sql server column is a float.

Answer 1

Most likely you'll have to find a different way to store and compare the two numbers since 7.1 and 7.10 are equal when just talking about numbers. You might have to go with a varchar field then create a function that compares the two values.

I would probably have two fields, major and minor and then sort by the major number first then the minor.

Answer 2

store it as a string, that is the only way those two numbers are different.

Answer 3

If you choose a numeric data type, there will be no difference between 7.1 and 7.10 at all.

What I would do in this case is actually split the column into two, an integer and decimal portion.

So 7.1 would be stored as 7 and 1, 7.10 would be stored as 7 and 10.

If it's a simple two-part (major/minor) number, that would work well enough, I think

Your query then becomes:

select major|'.'|minor, other_fields
from tbl
order by major, minor.

For a multi-part number (like chapter headings, 3.1.4.5.9 and so on), I'd still split it but probably use a separate table for holding the components.

Answer 4

I would use two integer columns. The first one would contain the left hand side of the number (7.1 => 7) and the other would contain the right hand side (7.1 => 1). I would derive the 7.1 value whenever I query the db by convert the columns values to string and appending them. You could then use the individual column values to sort.

The idea is that would you have a query like this: (sorry, I don't know how to convert types in a query off the top of my head):

Select string(col1) + string(col2)
from table
orderby col1, col2

Answer 5

Do you want 7.2 to be less than 7.10, too? Like with some versioning schemes?

If so, store the version number as two integer fields. To compare, you can compare each separately or use (left * 1000 + right) or similar.

Answer 6

You could use the values as a lookup to another table that stores their ordinal position.

CREATE TABLE Versions (
 version VARCHAR(10) PRIMARY KEY,
 ordinal INT NOT NULL
);

INSERT INTO Versions ('7.1', 1);
INSERT INTO Versions ('7.10', 2);

Make your table reference Versions with a foreign key:

CREATE TABLE MyTable (
 . . .
 version VARCHAR(10) NOT NULL,
 FOREIGN KEY (version) REFERENCES Versions(version)
);

Now you can join to this table and sort by the ordinal:

SELECT m.*
FROM MyTable m
JOIN Versions v USING (version)
ORDER BY v.ordinal;

For even greater flexibility, make ordinal a FLOAT so you can add new entries in between existing entries without having to renumber them all.

Answer 7

You don't say what you're actually using these numbers for, but if it's for versioning you should look at using the Version class in your .NET code.

That way you get all the benefits of the built in class (comparisons etc) and when you need to store them in a database, call Version.ToString() to get the result as a string. This can then be easily stored as a varchar field.

To get a previously stored version back from the database, retrieve the varchar field and then pass it as a string parameter to the Version class constructor.

You could also use the Version.Major, Version.Minor properties to store and retrieve the values in separate integer database fields, if you prefer.