On Wed, Jan 13, 2010 at 05:11:16PM -0800, Gary Baranzini scratched on the wall:
> If I do: SELECT point_number FROM mytable ORDER BY point_number; > I get the following: > > 1010.01 > 1010.09 > 11.01 > 11.23 > 55.09 > 77.01 > A03 > A06 Yes, that's the text sort order. > When I do the following: > SELECT point_number FROM mytable ORDER BY point_number+0; > > I get the following: > > A03 > A06 > 11.01 > 11.23 > 55.09 > 77.01 > 1010.01 > 1010.09 And that's the numeric order. By making an expression, you're attempting to convert the text to a number before the sort. Since 'A..' isn't a valid part of a number, it is translated to 0.0, which makes it sort first in this case. > I would like to have them ordered in the following sequence: > > 11.01 > 11.23 > 55.09 > 77.01 > 1010.01 > 1010.09 > A03 > A06 > > How can I get the A03, A06 at the end of the list? Make the numbers numbers, not text values. The natural sort-order for types is NULL, numeric (INTEGER & REAL mixed), TEXT, BLOB. If the numbers were actual REAL values and not TEXT representations of numbers, you'd get the sort order you describe here-- numbers in their natural order, followed by TEXT values in their own natural order. SQLite lets you mix types within a column, so this isn't a problem. I'm guessing you could also use CAST, CASE, and glob() to do the correct conversions for you... use glob() to look for strings that have characters other than [-+.eE0-9] and use CASE to return the TEXT value as-is or CAST it to a number if it looks like a valid number. My brain hurts too much at the moment to actually figure that out right here and now, however. I'll let Igor do that. Sort orders: http://www.sqlite.org/datatype3.html#comparisons Translation rules: http://www.sqlite.org/c3ref/column_blob.html (see table) -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
