I know that ORDER BY sorts result but I want to sort a table BEFORE it gets queried and am not sure of the syntax. Here's my test program but...I'm not sure how to PRE-SORT tables t_x and t_y (by column pos) BEFORE I do the SELECT BETWEEN on THEM i.e. I purposefully inserted t_d row 1,1,1 to see if it would come out first ie as 1. It doesn't any help much appreciated. Apologies if my question isn't clear.
#include "stdafx.h" #include "stdio.h" #include "sqlite3.h" #include<stdlib.h> //malloc #include<string.h> //strlen #include<assert.h> //assert /*i've made these global so...both Create_database AND Query_database can SEE them ie it shows what vars need to be common*/ int res, ind = 0; char** sql; sqlite3_stmt *stmt; sqlite3* db=NULL; char* err=0; static int Open_db(char* flnm){ if (!strlen(flnm)){ res=sqlite3_open(":memory:",&db); } else{ res=sqlite3_open(flnm,&db); } if (!db){ printf("Open_db() failed\n"); } return res; } void Close_db(){ res = sqlite3_close(db); } int Exec(char * s){ res = sqlite3_exec(db,s,0,0,0); if ( res ){ printf( "Exec error re %s %s\n", s, sqlite3_errmsg(db) ); } assert(res==0); //so you can concentrate on 1st error return res; } int _tmain(int argc, _TCHAR* argv[]){ //default project main Open_db(""); //======================================= Exec( "CREATE TABLE IF NOT EXISTS t_x(" "pos integer UNIQUE NOT NULL," "txt text NOT NULL" ")" ); Exec( "INSERT INTO t_x VALUES(1,'x1')" ); Exec( "INSERT INTO t_x VALUES(2,'x2')" ); //======================================= Exec( "CREATE TABLE IF NOT EXISTS t_y(" "pos integer UNIQUE NOT NULL," "txt text NOT NULL" ")" ); Exec( "INSERT INTO t_y VALUES(1,'y1')" ); Exec( "INSERT INTO t_y VALUES(2,'y2')" ); //======================================= Exec( "CREATE TABLE IF NOT EXISTS t_d(" "xpos integer NOT NULL," "ypos integer NOT NULL," "d float " ")" ); /* table layout see onenote thoughts diary me at 30/06/2011 08:42 y x-> | 1,2 V 3,4 x y data V V V */ Exec( "INSERT INTO t_d VALUES(1,2,3)" ); Exec( "INSERT INTO t_d VALUES(2,1,2)" ); Exec( "INSERT INTO t_d VALUES(2,2,4)" ); Exec( "INSERT INTO t_d VALUES(1,1,1)" ); //======================================= // http://dcravey.wordpress.com/2011/03/21/using-sqlite-in-a-visual-c-application/ //========= this block from url albeit modified by me ========================== const char* sqlSelect = "SELECT d FROM t_d " "where xpos in " "(SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2') "; "AND ypos in " "(SELECT pos FROM t_y WHERE txt BETWEEN 'y1' AND 'y2')"; char** results = NULL; int rows, columns; res = sqlite3_get_table(db, sqlSelect, &results, &rows, &columns, &err); if (res){ //printf( "error in sqlite3_get_table %s\n", s, sqlite3_errmsg(db) ); sqlite3_free(err); } else{ // Display Table for (int rowCtr = 0; rowCtr <= rows; ++rowCtr){ for (int colCtr = 0; colCtr < columns; ++colCtr){ int cellPosition = (rowCtr * columns) + colCtr; printf( "%s\t", results[cellPosition] ); } printf( "\n"); } } sqlite3_free_table(results); //============================================================================== Close_db(); sqlite3_free(err); getchar(); return 0; } _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users