Hello,
Regardless of how I construct the underlying table (use/do not use primary key, auto increment etc), when using db:nrows with an sql statement that includes an ORDER BY clause, the result set is sorted in ascending order of id and not in the order suggested by the ORDER BY clause.
Some sample code below:
-- include sqlite
require "sqlite3"
-- open test.db (if test.db doesn't exist it will be created)
local path = system.pathForFile("test.db", system.DocumentsDirectory)
db = sqlite3.open( path )
-- start fresh each time for testing
local dropTable = [[DROP TABLE highscores;]]
db:exec( dropTable )
-- version 1 primary key, auto increment
-- following test cases fail to order results as spec in sql
local tableSetup = [[CREATE TABLE IF NOT EXISTS highscores ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "multiplier" NUMERIC, "mode" TEXT, "score" NUMERIC);]]
--local tableSetup = [[CREATE TABLE IF NOT EXISTS highscores ("id" INTEGER, "multiplier" NUMERIC, "mode" TEXT, "score" NUMERIC);]]
--local tableSetup = [[CREATE TABLE IF NOT EXISTS highscores ("id" INTEGER PRIMARY KEY NOT NULL, "multiplier" NUMERIC, "mode" TEXT, "score" NUMERIC);]]
db:exec( tableSetup )
-- add some records
local multiplier = 2
local mode = "timed"
local score = 10000
local sql =[[INSERT INTO highscores VALUES (NULL, ']]..multiplier..[[',']]..mode..[[',']]..score..[[');]]
db:exec( sql )
local multiplier = 2
local mode = "timed"
local score = 20000
local sql =[[INSERT INTO highscores VALUES (NULL, ']]..multiplier..[[',']]..mode..[[',']]..score..[[');]]
db:exec( sql )
local multiplier = 2
local mode = "timed"
local score = 30000
local sql =[[INSERT INTO highscores VALUES (NULL, ']]..multiplier..[[',']]..mode..[[',']]..score..[[');]]
db:exec( sql )
local multiplier = 2
local mode = "timed"
local score = 40000
local sql =[[INSERT INTO highscores VALUES (NULL, ']]..multiplier..[[',']]..mode..[[',']]..score..[[');]]
db:exec( sql )
local multiplier = 4
local mode = "timed"
local score = 10000
local sql =[[INSERT INTO highscores VALUES (NULL, ']]..multiplier..[[',']]..mode..[[',']]..score..[[');]]
db:exec( sql )
local multiplier = 4
local mode = "timed"
local score = 20000
local sql =[[INSERT INTO highscores VALUES (NULL, ']]..multiplier..[[',']]..mode..[[',']]..score..[[');]]
db:exec( sql )
local multiplier = 4
local mode = "timed"
local score = 30000
local sql =[[INSERT INTO highscores VALUES (NULL, ']]..multiplier..[[',']]..mode..[[',']]..score..[[');]]
db:exec( sql )
local multiplier = 4
local mode = "timed"
local score = 40000
local sql =[[INSERT INTO highscores VALUES (NULL, ']]..multiplier..[[',']]..mode..[[',']]..score..[[');]]
db:exec( sql )
-- print the sqlite version to the terminal
print( "version " .. sqlite3.version() )
-- rather than output results in 'id' order, we are attempting to output the three top scores in order
-- of highest to lowest. we attempt this with the following sql
-- unfortunately the order by clause is ignored or perhaps the db:nrows call can only operate in order of id
-- which may require you to apply your own sort function to the results array?
for row in db:nrows("SELECT \* FROM highscores WHERE multiplier = 2 ORDER BY score DESC LIMIT 1,3") do
local text = row.id.." "..row.multiplier.." "..row.mode.." "..row.score
local t = display.newText(text, 20, 30 \* row.id, null, 16)
t:setTextColor(255,0,255)
end
-- close the db on event applicationExit
local function onSystemEvent( event )
if( event.type == "applicationExit" ) then
db:close()
end
end
-- setup the system listener to catch applicationExit
Runtime:addEventListener( "system", onSystemEvent ) I found several older unresolved posts along similar lines.
The basic select and even limit clauses work, alas the ORDER BY clause appears to get bumped.
Any assistance greatly appreciated.
Kind regards, Andrew [import]uid: 12361 topic_id: 6607 reply_id: 306607[/import]