No. Column name and table referents (identifiers) must be specified explicitly (as part of the command) and MUST NOT be bound parameters. You are asking to sort by the value 1 for all rows, which means that the output is in "visitation order" since the ORDER BY value is the same for all rows ...
That is to say that you cannot use a bound parameter (? for example) it the locations that have a ? in the following query, and have that bound parameter refer to an index name, column name, or table name (or any defined identifier whatsoever) -- it will only refer to the "value" given the bound parameter or result in a syntax error if a "value" is not permitted at that part of the syntax: SELECT ? from ? INDEXED BY ? WHERE ? = 17 GROUP BY ? ORDER BY ?; --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Danny Milosavljevic >Sent: Monday, 29 January, 2018 12:14 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] "ORDER BY ?" sqlite3_bind_int OK, but does weird >things > >Hi, > >I'm trying to prepare a statement for "SELECT a FROM t ORDER BY ?" >and >then sqlite3_bind_int the parameter to 1 (on sqlite 3.19.3). > >Expected result: Orders result by column "a", in ascending order. >Observed result: Orders in some strange order. > >I also tried sqlite3_bind_int64, didn't change the result. > >Should this use case work? > >To reproduce: > >OK case (prints 2 and then 5): > >#include <stdio.h> >#include <stdlib.h> >#include <sqlite3.h> > >int main() { > sqlite3* db; > sqlite3_stmt* stmt; > printf("%s\n", sqlite3_libversion()); > if (sqlite3_open(":memory:", &db) != SQLITE_OK || > sqlite3_exec(db, "CREATE TABLE t(a int);" > "INSERT INTO t(a) VALUES (5);" > "INSERT INTO t(a) VALUES (2);", NULL, 0, >NULL) != SQLITE_OK || > sqlite3_prepare_v2(db, "SELECT a FROM t ORDER BY 1", -1, >&stmt, 0) != SQLITE_OK || > sqlite3_step(stmt) != SQLITE_ROW) > abort(); > printf("%d\n", sqlite3_column_int(stmt, 0)); > if (sqlite3_step(stmt) != SQLITE_ROW) > abort(); > printf("%d\n", sqlite3_column_int(stmt, 0)); > return 0; >} > >Not OK case (prints 5 and then 2): > >#include <stdio.h> >#include <stdlib.h> >#include <sqlite3.h> > >int main() { > sqlite3* db; > sqlite3_stmt* stmt; > printf("%s\n", sqlite3_libversion()); > if (sqlite3_open(":memory:", &db) != SQLITE_OK || > sqlite3_exec(db, "CREATE TABLE t(a int);" > "INSERT INTO t(a) VALUES (5);" > "INSERT INTO t(a) VALUES (2);", NULL, 0, >NULL) != SQLITE_OK || > sqlite3_prepare_v2(db, "SELECT a FROM t ORDER BY ?", -1, >&stmt, 0) != SQLITE_OK || > sqlite3_bind_int(stmt, 1, 1) != SQLITE_OK || > sqlite3_step(stmt) != SQLITE_ROW) > abort(); > printf("%d\n", sqlite3_column_int(stmt, 0)); > if (sqlite3_step(stmt) != SQLITE_ROW) > abort(); > printf("%d\n", sqlite3_column_int(stmt, 0)); > return 0; >} > >Also OK but not that useful: > >#include <stdio.h> >#include <stdlib.h> >#include <sqlite3.h> > >int main() { > sqlite3* db; > sqlite3_stmt* stmt; > printf("%s\n", sqlite3_libversion()); > if (sqlite3_open(":memory:", &db) != SQLITE_OK || > sqlite3_exec(db, "CREATE TABLE t(a int);" > "INSERT INTO t(a) VALUES (5);" > "INSERT INTO t(a) VALUES (2);", NULL, 0, >NULL) != SQLITE_OK || > sqlite3_prepare_v2(db, "SELECT a FROM t ORDER BY 1, ?", - >1, &stmt, 0) != SQLITE_OK || > sqlite3_bind_int(stmt, 1, 1) != SQLITE_OK || > sqlite3_step(stmt) != SQLITE_ROW) > abort(); > printf("%d\n", sqlite3_column_int(stmt, 0)); > if (sqlite3_step(stmt) != SQLITE_ROW) > abort(); > printf("%d\n", sqlite3_column_int(stmt, 0)); > return 0; >} >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
