What is up with SQLite Sorting? is not working?

[DarkAngelBGE]

#include <SQLite.au3>
#include <SQLite.dll.au3>

Local $hQuery, $aRow, $sMsg
_SQLite_Startup ()
_SQLite_Open (); open :memory: Database
_SQLite_Exec (-1, "CREATE TABLE aTest (a,b,c,stimmen);"); CREATE a Table
_SQLite_Exec (-1, "INSERT INTO aTest(a,b,c,stimmen) VALUES ('11','2','World','33');"); INSERT Data
_SQLite_Exec (-1, "INSERT INTO aTest(a,b,c,stimmen) VALUES ('15','3','something','77');"); INSERT Data
_SQLite_Exec (-1, "INSERT INTO aTest(a,b,c,stimmen) VALUES ('5','1','Hello','55');"); INSERT Data
_SQlite_Query (-1, "SELECT c FROM aTest ORDER BY stimmen DESC;", $hQuery); the query
While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK
    $sMsg = $aRow[0]
    msgbox(2,$sMsg,$sMsg)
WEnd
_SQLite_Exec (-1, "DROP TABLE aTest;"); Remove the table
MsgBox(0,"SQLite","Get Data using a Query : " &  $sMsg )
_SQLite_Close()
_SQLite_Shutdown()

Sorting by "stimmen" (German word for votes) works perfect. Now let's sort by 'a':

#include <SQLite.au3>
#include <SQLite.dll.au3>

Local $hQuery, $aRow, $sMsg
_SQLite_Startup ()
_SQLite_Open (); open :memory: Database
_SQLite_Exec (-1, "CREATE TABLE aTest (a,b,c,stimmen);"); CREATE a Table
_SQLite_Exec (-1, "INSERT INTO aTest(a,b,c,stimmen) VALUES ('11','2','World','33');"); INSERT Data
_SQLite_Exec (-1, "INSERT INTO aTest(a,b,c,stimmen) VALUES ('15','3','something','77');"); INSERT Data
_SQLite_Exec (-1, "INSERT INTO aTest(a,b,c,stimmen) VALUES ('5','1','Hello','55');"); INSERT Data
_SQlite_Query (-1, "SELECT c FROM aTest ORDER BY a DESC;", $hQuery); the query
While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK
    $sMsg = $aRow[0]
    msgbox(2,$sMsg,$sMsg)
WEnd
_SQLite_Exec (-1, "DROP TABLE aTest;"); Remove the table
MsgBox(0,"SQLite","Get Data using a Query : " &  $sMsg )
_SQLite_Close()
_SQLite_Shutdown()

..and the result is completely wrong displaying "Hello", "something", "World" in that order. What is wrong?

[DarkAngelBGE]

Nobody?

[DarkAngelBGE]

gah, it compares the number as string because there are quotes around them. Too bad. :S

[The Kandie Man]

Yeah, it is an error on the help documentation. Numbers should not be entered as strings.

This is wrong:

_SQLite_Exec (-1, "INSERT INTO aTest(a,b,c,stimmen) VALUES ('11','2','World','33');"); INSERT Data
_SQLite_Exec (-1, "INSERT INTO aTest(a,b,c,stimmen) VALUES ('15','3','something','77');"); INSERT Data
_SQLite_Exec (-1, "INSERT INTO aTest(a,b,c,stimmen) VALUES ('5','1','Hello','55');"); INSERT DataoÝ÷ Ù8b²++mjëh×6_SQLite_Exec (-1, "INSERT INTO aTest(a,b,c,stimmen) VALUES (11,2,'World',33);"); INSERT Data
_SQLite_Exec (-1, "INSERT INTO aTest(a,b,c,stimmen) VALUES (15,3,'something',77);"); INSERT Data
_SQLite_Exec (-1, "INSERT INTO aTest(a,b,c,stimmen) VALUES (5,1,'Hello',55);"); INSERT Data

- The Kandie Man ;-)

Edited June 19, 2007 by The Kandie Man

[McGod]

Or you could use

#include <SQLite.au3>
#include <SQLite.dll.au3>

Local $hQuery, $aRow, $sMsg
_SQLite_Startup ()
_SQLite_Open (); open :memory: Database
_SQLite_Exec (-1, "CREATE TABLE aTest (a NUMERIC,b,c,stimmen);"); CREATE a Table
_SQLite_Exec (-1, "INSERT INTO aTest(a,b,c,stimmen) VALUES ('11','2','World','33');"); INSERT Data
_SQLite_Exec (-1, "INSERT INTO aTest(a,b,c,stimmen) VALUES ('15','3','something','77');"); INSERT Data
_SQLite_Exec (-1, "INSERT INTO aTest(a,b,c,stimmen) VALUES ('5','1','Hello','55');"); INSERT Data
_SQlite_Query (-1, "SELECT c FROM aTest ORDER BY a DESC;", $hQuery); the query
While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK
    $sMsg = $aRow[0]
    msgbox(2,$sMsg,$sMsg)
WEnd
_SQLite_Exec (-1, "DROP TABLE aTest;"); Remove the table
MsgBox(0,"SQLite","Get Data using a Query : " &  $sMsg )
_SQLite_Close()
_SQLite_Shutdown()

It was sorting a as as string, so use a NUMERIC it will know that everything in the column is numbers and to sort accordingly.

the reason stimmen worked is because there all two digits.

Edited June 19, 2007 by Chip

[DarkAngelBGE]

Yeah it works now.

Now how can I sort a listbox that contains such values:

33 - some name

11 - some other name

22 - my name

to output it as:

33 - some name

22 - my name

11 - some other name

Is this possible?

[The Kandie Man]

I helped him over MSN and he got it to work. He used _GUICtrlListViewSort() to do it.

- The Kandie Man ;-)

Edited June 19, 2007 by The Kandie Man