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
