> 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
