> Its not a very good example because the two are adjacent and 'x1' and 'x2' > sound like they're adjacent too.
They are not adjacent - 'x1123456' and a lot of other strings starting with 'x1' are between them. > I'm only interested in the results of BETWEEN when you're looking at x1 and > x2 from the pos order perspective Then David's query is a way to go. Pavel On Fri, Jul 1, 2011 at 1:04 PM, e-mail mgbg25171 <mgbg25...@blueyonder.co.uk> wrote: > 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 <paiva...@gmail.com> 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 >> <mgbg25...@blueyonder.co.uk> 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 <paiva...@gmail.com> 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 >> >> <mgbg25...@blueyonder.co.uk> 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 <paiva...@gmail.com> 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 >> >> >> <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 >> >> >> >> >> > _______________________________________________ >> >> > 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 >> > _______________________________________________ > 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
