Sign in to follow this  
Followers 0
DarkAngelBGE

What is up with SQLite Sorting? is not working?

7 posts in this topic

#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?

Share this post


Link to post
Share on other sites



Nobody? :rolleyes:

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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 by The Kandie Man

"So man has sown the wind and reaped the world. Perhaps in the next few hours there will no remembrance of the past and no hope for the future that might have been." & _"All the works of man will be consumed in the great fire after which he was created." & _"And if there is a future for man, insensitive as he is, proud and defiant in his pursuit of power, let him resolve to live it lovingly, for he knows well how to do so." & _"Then he may say once more, 'Truly the light is sweet, and what a pleasant thing it is for the eyes to see the sun.'" - The Day the Earth Caught Fire

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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 by Chip

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

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

- The Kandie Man ;-)

Edited by The Kandie Man

"So man has sown the wind and reaped the world. Perhaps in the next few hours there will no remembrance of the past and no hope for the future that might have been." & _"All the works of man will be consumed in the great fire after which he was created." & _"And if there is a future for man, insensitive as he is, proud and defiant in his pursuit of power, let him resolve to live it lovingly, for he knows well how to do so." & _"Then he may say once more, 'Truly the light is sweet, and what a pleasant thing it is for the eyes to see the sun.'" - The Day the Earth Caught Fire

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0