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
