2009/2/23 John Elrick <john.elr...@fenestra.com>: . . . > > The problem: Is there any existing collation solution that respects > numeric values intermixed with alpha? As an example, if we have the > following rows: > > 1 > a > 10 > 4 > 51 > 9 > > traditional alpha-numeric sorting would sort them as follows: > > 1 > 10 > 4 > 51 > 9 > a > > What the customer desires is for the sorting to treat strictly numeric > values as though they were indeed numerics: > > 1 > 4 > 9 > 10 > 51 > a > > Thanks for any feedback, including "nope, you have to roll your own". > > > John Elrick > Fenestra Technologies
Hi John, If your data is in an integer column then an order by on the data column gives what you ask for: SQLite version 3.4.2 Enter ".help" for instructions sqlite> sqlite> sqlite> create table tst( id integer primary key, data integer ); sqlite> sqlite> insert into tst( data ) values( 1 ); sqlite> insert into tst( data ) values( 'a' ); sqlite> insert into tst( data ) values( 10 ); sqlite> insert into tst( data ) values( 4 ); sqlite> insert into tst( data ) values( 51 ); sqlite> insert into tst( data ) values( 9 ); sqlite> sqlite> select data from tst order by data; 1 4 9 10 51 a Since you are asking the question, I guess that is not your situation. So use a case expression and cast in the order by clause: sqlite> sqlite> create table tst2( id integer primary key, data text ); sqlite> sqlite> insert into tst2( data ) values( 1 ); sqlite> insert into tst2( data ) values( 'a' ); sqlite> insert into tst2( data ) values( 10 ); sqlite> insert into tst2( data ) values( 4 ); sqlite> insert into tst2( data ) values( 51 ); sqlite> insert into tst2( data ) values( 9 ); sqlite> sqlite> sqlite> select data from tst2 order by data; 1 10 4 51 9 a sqlite> sqlite> select data from tst2 order by case cast( data as integer )=data when 1 then cast( data as integer ) else data end; 1 4 9 10 51 a Rgds, Simon _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
