[Gambas-user] Tableview format

[ron]

On Friday 18 May 2007 00:38, nogueira_jr wrote:
> Fabien Bodard escreveu: 
> > 2007/5/17, nogueira_jr <nogueira_jr at ...172...>: 
> >> Hi All: 
> >> 
> >> Please, help me in the TableView: 
> >> 
> >> I use SQLite3 and my table have: 
> >> codigo      alfanumerico 13 
> >> preco        float 
> >> 
> >> Problem 1: Tableview show the order: 
> >> CODIGO 
> >> 1 
> >> 10 
> >> 11 
> >> 2 
> >> 3 ..... 
> >> 
> >> I want the correct order: 
> >> 
> >> 1 
> >> 2 
> >> 3 
> >> .... 
> >> 9 
> >> 10 
> >> 11 
> > 
> > 
> > use the sql statement : 
> > "SELECT * FROM MyTable ORDER BY codigo 
> Already I am using the ORDER BY codigo ..... 
> ????? 
> 

SELECT *  FROM MyTable  ORDER BY CONVERT(INT,codigo) ASC (mysql)


http://article.gmane.org/gmane.comp.db.sqlite.general/28937

quote:

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. ;-)


Once I did it with something like this for mysql:

select price from products order by myprice asc

select price as myprice from products order by myprice asc

select concat("        ",price) as myprice  from products order by myprice

select right(concat("        ",price),10) as myprice  from products order by myprice

first add in front spaces (10 or so) concat was the mysql function
then get the right part for 10 characters, right() used here is not the real 
correct function for mysql but there is a function that works that way.
Mostly the function around the selected field works also on the ORDER BY field
Here it has the advanced of right adjustment include. :)


I do not know sqlite does support functions on select fields.

So I hope the first from the mame.org site will fitt for you.