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 <[email protected]> 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 > <[email protected]> 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 <[email protected]> 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 <[email protected]> > >> wrote: > >> > > >> > On 1 Jul 2011, at 3:07pm, Alessandro Marzocchi wrote: > >> > > >> >> 2011/7/1 Simon Slavin <[email protected]> > >> >> > >> >>> 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 > >> > [email protected] > >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > > >> _______________________________________________ > >> sqlite-users mailing list > >> [email protected] > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > > _______________________________________________ > > sqlite-users mailing list > > [email protected] > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
