Hi,
i apologize beforehand if my post does not answer your question
directly. It seems to me that you may be missing a basic concept.
Data in an SQL table is never sorted in itself. So, you can not sort a
table before you query it.
If you select data without an "order by" - clause, the order in which
you get the data is arbirtary and may also change from time to time. So,
if you want to retrieve rows in a certain order, you have to add an
"order by" clause to your select statement.
If you want to speed up such a query, you can create an index.
In your case pos seems to be a candidate for a primary key, because it
is unique. So, create your table like this:
CREATE TABLE IF NOT EXISTS t_x(
"pos integer primary key, "
"txt text NOT NULL"
);
Then, data is indexed automatically by pos, which comes near to your
intention. However, you must always add an "order by pos" clause to your
select statement(s).
Perhaps you might want to read up on primary keys and indexes. This is
not sqlite specific but a feature of SQL.
hth
Martin
Am 01.07.2011 11:16, schrieb e-mail mgbg25171:
> 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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
