Re: [sqlite] Ensure that query acts on PRE-SORTED tables

Thank you Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

e-mail mgbg25171 wrote: > 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

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

The between operator is order dependent. This variation might work: SELECT d from T_d inner join (select min(pos) as xMin, max(pos) as yMax FROM T_x WHERE txt = '1990' OR txt='1991') as xcriteria on xPos between xMin and xMax inner join (select min(pos) as yMin, max(pos) as yMax FROM T_y WHERE

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

RE txt = 'cogs') AND (SELECT pos FROM T_y WHERE txt = 'sg expenses'); David --- On Fri, 7/1/11, e-mail mgbg25171 <mgbg25...@blueyonder.co.uk> wrote: > From: e-mail mgbg25171 <mgbg25...@blueyonder.co.uk> > Subject: Re: [sqlite] Ensure that query acts on PRE-SORT

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

Pavel and David... I just moved some of the table insertions around to change their rowid values and the results are STILL coming out in pos order so... which wasn't what I was getting with my attempts so... Thank you very much indeed for your advice and solution. It is appreciated! On 1 July

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

> If you believe that result of a query differs depending on what order SQLite processes rows in then you are wrong. I am wrong! On 1 July 2011 18:38, e-mail mgbg25171 wrote: > Just to clarify further "pos BETWEEN txt = 1990 and 1991" as its stands > looks (to my

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

Just to clarify further "pos BETWEEN txt = 1990 and 1991" as its stands looks (to my naive eye) like its going to return 1 3 2 and if you ORDER BY pos then it's going to return 1 2 3 Neither of which is what I want. By contrast if you were to "sort" the table FIRST then "pos BETWEEN txt = 1990 and

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

Here's an example of what I'm trying to do with my query t_x rowid=1,pos=1, txt=1990 rowid=2,pos=3, txt=1992 rowid=3,pos=2, txt=1991 t_y rowid=1,pos=3,txt="sg expenses" rowid=2,pos=2,txt="cogs" rowid=3,pos=1,txt='revenue' t_d rowid=1,xpos=1,ypos=1,d=$1 rowid=2,xpos=1,ypos=2,d=$2

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

> Its not a very good example because the two are adjacent and 'x1' and 'x2' > sound like they're adjacent too. They are not adjacent - 'x1123456' and a lot of other strings starting with 'x1' are between them. > I'm only interested in the results of BETWEEN when you're looking at x1 and > x2

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

Pavel, David Thanks for bearing with me... > "txt BETWEEN 'x1' AND 'x2'" you mean those rows between the row where txt = 'x1' and the row where txt = 'x2' Yes that's right. Its not a very good example because the two are adjacent and 'x1' and 'x2' sound like they're adjacent too. I'm only

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

> I'll certainly try >>SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos; > but I need x1 and x2 to be ordered before BETWEEN sees them rather than the > results just sorted by pos. Maybe I've missed something in this conversation? Please clarify how "results sorted by pos" will be

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

r.co.uk> > Subject: Re: [sqlite] Ensure that query acts on PRE-SORTED tables > To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> > Date: Friday, July 1, 2011, 12:02 PM > Thx for your suggestion... > Yes "BY ORDER BY pos" has to be in the

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

I'll certainly try >SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos; but I need x1 and x2 to be ordered before BETWEEN sees them rather than the results just sorted by pos. I've just done this. const char* sqlSelect ="SELECT d FROM t_d " "WHERE

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

> It strikes me that > SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' > needs to be operating on the results returned by > SELECT * FROM t_x BY ORDER BY pos > ie another level of query is required but I'm not sure of how you insert it. I don't understand what you are talking about here. You

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

Thx for your suggestion... Yes "BY ORDER BY pos" has to be in there somewhere. It strikes me that SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' needs to be operating on the results returned by SELECT * FROM t_x BY ORDER BY pos ie another level of query is required but I'm not sure of how you

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

> What I want to do is...make sure that when I say BETWEEN I really mean eg > BETWEEN x1 and x2 when you look at the table as if it's ordered by pos and > not rowid. So, can you add "ORDER BY pos" to your queries? Pavel On Fri, Jul 1, 2011 at 11:04 AM, e-mail mgbg25171

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

Thank you all for your responses. I had to go out after posting and have just come back. My concern is with... SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' and SELECT pos FROM t_y WHERE txt BETWEEN 'y1' AND 'y2'. t_x and t_y are dimension tables. that hold the x and y margins of a

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

>> Putting the 'ORDER BY' clause in view won't work? > > It will work just fine, in that the results you see will appear in the ORDER > you asked for. I believe that's not always true and is not required by SQL standard. Most probably 'select * from view_name' will return rows in the order

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

On 1 Jul 2011, at 3:07pm, Alessandro Marzocchi wrote: > 2011/7/1 Simon Slavin > >> On 1 Jul 2011, at 11:20am, Alessandro Marzocchi wrote: >> >>> Isn't it possible to use a view for that? >> >> You can use a VIEW if you want, but VIEWs don't sort the table either. A >>

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

2011/7/1 Simon Slavin > > On 1 Jul 2011, at 11:20am, Alessandro Marzocchi wrote: > > > Isn't it possible to use a view for that? > > You can use a VIEW if you want, but VIEWs don't sort the table either. A > VIEW is just a way of saving a SELECT query. When you consult

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

On 1 Jul 2011, at 11:20am, Alessandro Marzocchi wrote: > Isn't it possible to use a view for that? You can use a VIEW if you want, but VIEWs don't sort the table either. A VIEW is just a way of saving a SELECT query. When you consult the VIEW SQLite executes the SELECT. Simon.

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

2011/7/1 Mohit Sindhwani > On 1/7/2011 5:37 PM, Martin.Engelschalk wrote: > > 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.

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

On 1/7/2011 5:37 PM, Martin.Engelschalk wrote: > 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.

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

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