SQLite Discussion - Sorting Order: TEXT vs NUMBER

One of our customers noticed a problem with our Switch List Editor in the Managed Switch Port Mapping Tool. The list ordering relies on a column in a table that we'll call ListIndex. The DDL for the table had defined the column as [ListIndex] TEXT. The index being saved in the column was the numbers zero through however many rows there are for that set of entries (this is not the main constraint column, just an indexing column within a set of rows).

If you did:

'SELECT * FROM theTable WHERE mainConstraintCol='rowSet' ORDER BY ListIndex ASC'

we expected to get a set of rows indexed 0, 1, 2, 3, 4 etc. in that order. That worked fine until there were more than 10 entries in a set of rows. Then the sorting would be 0, 1, 10, 11, 12, 13, 14, 2, 3, 4, 5, 6, 7, 8, 9. Obviously it is the creect sorting if the column is of type TEXT, but wrong since we are interested in the numbers themselves.

The solution was to change the DDL to this: [ListIndex] NUMBER (or INTEGER). That way the ORDER BY sorting ends up correctly in numeric order for lists larger than 10: 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, etc.

Changing the DDL works OK if you are creating the table every time, but this table is persistent between application sessions. Because SQLite (to my knowledge) has no ALTER TABLE method for changing an existing table DDL Type from TEXT to NUMBER, I had to do the create temp table, copy contents from original, DROP the original table and recreate it correctly, then copy the temp table contents back to the original. A pain, but functional method. I would be interested in any other simpler methods.

Moral of this story is to be sure of your column typing when you define a table in SQLite especially if you are depending on the contents of that column to index something else.

SQLite: www.sqlite.org
Managed Switch Port Mapping Tool: www.SwitchPortMapper.com