> 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
