Chad Woolley <[EMAIL PROTECTED]> wrote: > We recently upgraded to sqlite3, and I cannot figure out how to get a > TEXT type field to sort numerically (it's for a Trac report). For > example, it is sorting 10,100,20,200 instead of 10, 20, 100, 200. > > sqlite> select distinct value from ticket_custom where name='rank' > order by value;
If you want a column to sort numerically, the best thing to do is to make the column type numeric. You'll still be able to store text, but anything that looks like a number will sort first and in numerical order. Failing that, you could do: ... ORDER BY cast(value AS int); But using a cast() in the ORDER BY clause will defeat any attempt by the optimizer to use an index to aid in sorting. This may or may not be an issue for you. -- D. Richard Hipp <[EMAIL PROTECTED]>
