Pavel, David Thanks for bearing with me... > "txt BETWEEN 'x1' AND 'x2'" you mean those rows between the row where txt = 'x1' and the row where txt = 'x2' Yes that's right. Its not a very good example because the two are adjacent and 'x1' and 'x2' sound like they're adjacent too. I'm only interested in the results of BETWEEN when you're looking at x1 and x2 from the pos order perspective ie doing the BETWEEN query on 'x1' and 'x2' would be useless from the rowid perspective/order. I'll write a better description of what I'm trying to do and come back.
On 1 July 2011 17:48, Pavel Ivanov <[email protected]> wrote: > > I'll certainly try > >>SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos; > > but I need x1 and x2 to be ordered before BETWEEN sees them rather than > the > > results just sorted by pos. > > Maybe I've missed something in this conversation? Please clarify how > "results sorted by pos" will be different from "x1 and x2 to be > ordered before BETWEEN sees". And tell us more clearly what results > you want to see from your query. We certainly see that you want to get > value of pos from all rows where value of txt lies between 'x1' and > 'x2'. Now do you want those results to be order by value of pos (add > ORDER BY pos), or by value of txt (add ORDER BY txt), or you want them > in a random order (do not add ORDER BY at all)? If you believe that > result of a query differs depending on what order SQLite processes > rows in then you are wrong. > > > Pavel > > > On Fri, Jul 1, 2011 at 12:34 PM, e-mail mgbg25171 > <[email protected]> wrote: > > I'll certainly try > >>SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos; > > but I need x1 and x2 to be ordered before BETWEEN sees them rather than > the > > results just sorted by pos. > > > > I've just done this. > > const char* sqlSelect = "SELECT d FROM t_d " > > "WHERE xpos in " > > "(SELECT pos FROM (SELECT * from t_x ORDER BY > > pos) WHERE txt BETWEEN 'x1' AND 'x2')"; > > //bit for t_y omitted. > > in the hope that (SELECT * from t_x ORDER BY pos) presents it's results > to > > SELECT BETWEEN in pos order. > > I am concerned about having to specify both xpos and pos and am not sure > how > > these two get reconciled. > > I am getting results but want to add more data to the tables to see whats > > going on. > > > > Thank you for your assistance though. > > > > On 1 July 2011 17:07, Pavel Ivanov <[email protected]> wrote: > > > >> > 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 > >> <[email protected]> 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 <[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 > >> > > >> _______________________________________________ > >> 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
