Yves Goergen schrieb: > On 13.05.2007 17:19 CE(S)T, Chris Wedgwood wrote: >> On Sun, May 13, 2007 at 05:07:16PM +0200, Yves Goergen wrote: >> >>> Ah, now I realised that I'd also like to have that "natural sorting", >>> meaning this: >>> >>> 2 >>> 8 >>> 9 >>> 10 >>> 11 >>> 23 >> select from <col> from table order by cast(<col> as text); > > I'm not sure what you wanted to say with this. I removed the first > "from" to make it work but it does the same as without the cast. It > still sorts strings beginning with "10" before those beginning with "2".
Yep, because the statement is wrong for your case. I guess he misunderstood you somehow. select <column> from <table> order by cast (<column> as integer) is what you wanted. But: If your column is a text column holding strings and numbers or strings beginning with numbers the statement above won't work as expected. All rows starting with alpha text will be placed at the beginning of the result. They all return 0 as the integer value and are not sorted but returned in the order they have been added to the table. If your column to sort on only holds integer values and you defined the column as INTEGER your "natural sorting" should be handled by SQLite. If you have strings *and* numbers in your columns then write a user defined collation sequence which handles this case. It's up to you. ;-) ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
