I'm not entirely sure what your data looks like, but I am thinking that when you say "txt BETWEEN 'x1' AND 'x2'" you mean those rows between the row where txt = 'x1' and the row where txt = 'x2'
If that is the case, maybe this will give you what you want: SELECT POS FROM T_x WHERE POS BETWEEN (SELECT POS FROM T_x WHERE txt = 'x1') AND (SELECT POS FROM T_x WHERE txt = 'x2'); Hopefully I have guessed your need somewhat correctly, 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-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 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 insert it. > I'll have a play. > > > > On 1 July 2011 16:12, Pavel Ivanov <paiva...@gmail.com> > wrote: > > > > 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 > > <mgbg25...@blueyonder.co.uk> > wrote: > > > 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 spreadsheet. > > > The margins will have an implied order shown by > pos > > > which will differ from the order in which rows > are added (represented by > > > rowid). > > > > > > 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. I hope that helps explain why pos > exists and is not rowid i.e. > > I > > > want to be able to "insert" and "delete" records > "!in between" the > > existing > > > ones or at least make it look like that even if > the records are > > physically > > > appended to the tables. > > > Hope this clarifies things and look forward to > your thoughts. > > > > > > > > > On 1 July 2011 15:30, Pavel Ivanov <paiva...@gmail.com> > wrote: > > > > > >> >> 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 > > >> written in the view. But 'select * from > view_name where some_column = > > >> some_value' can already return rows in > completely different order. And > > >> 'select * from table_name, view_name where > some_condition' will almost > > >> certainly ignore any ORDER BY in the view. > > >> > > >> So ORDER BY in the view doesn't guarantee you > anything. > > >> > > >> > > >> Pavel > > >> > > >> > > >> On Fri, Jul 1, 2011 at 10:19 AM, Simon Slavin > <slav...@bigfraud.org> > > >> wrote: > > >> > > > >> > On 1 Jul 2011, at 3:07pm, Alessandro > Marzocchi wrote: > > >> > > > >> >> 2011/7/1 Simon Slavin <slav...@bigfraud.org> > > >> >> > > >> >>> 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. > > >> >> > > >> >> 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. > > >> > > > >> > However, it has no influence on how data > is stored. In fact no table > > >> data is stored for a VIEW at all. The > thing stored is the parameters > > given > > >> when you created the VIEW. Every time > you refer to a VIEW in a SQL > > >> statement SQL goes back and looks at the VIEW > specification again. > > >> > > > >> > Simon. > > >> > > _______________________________________________ > > >> > 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 > > >> > > > _______________________________________________ > > > 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 > > > _______________________________________________ > 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
