how to get the correct sort order for integers?

[davos1]

Hi,
I created a table with a field:
d integer key asc.
then
select * from table order by d asc;

if I open the table, the schema is: d integer key asc., and the info seems to be stored like integer numbers.

the problem is when rendering the fields the order of the data appears: 1, 10,11,2,3
does any knows how to get the data in a correct ascendent number: 1,2,3,10,11 ?

thanks a lot
;D

[kokenge]

This may sound confusing to some, but SQLite does not care what a field attributes are.
If you use relational databases for a while you will come to appreciate it.

So for example if you have a field d defined as INTEGER, you can put characters in it.
Same with character fields - you can put numbers in them.

For example assume we have a table aa with field d integer.
If you INSERT into aa VALUES ('1') it's a number
If you INSERT into aa VALUES (1) it's a number
If you INSERT into aa VALUES ('1 ') it's a character since it has a blank after the 1
If you INSERT into aa VALUES (' 1') it's a character since it has a blank before the one.
If you INSERT into aa VALUES (' abc') it's a character.

SQLite notices the blank and since it is enclosed in a single quote it has to account for it. So it has no choice but to store it as a character..

As a rule if you have numbers, it is best not to enclose them in qoutes. That way it will ignore leading and trailing blanks.

The same rules apply to a field defined as text or varchar etc.

Hope this helps...

[davos1]

thanks,
I was inserting numbers like '1' without spaces, then I removed the '' and created the table again.
it seems that it is working now,