> 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 should write it like this: SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos; Pavel On Fri, Jul 1, 2011 at 12:02 PM, e-mail mgbg25171 <mgbg25...@blueyonder.co.uk> wrote: > 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
