Just to clarify further "pos BETWEEN txt = 1990 and 1991" as its stands looks (to my naive eye) like its going to return 1 3 2 and if you ORDER BY pos then it's going to return 1 2 3 Neither of which is what I want. By contrast if you were to "sort" the table FIRST then "pos BETWEEN txt = 1990 and 1991" would return 1 2 and this is what I want.
On 1 July 2011 18:24, e-mail mgbg25171 <mgbg25...@blueyonder.co.uk> wrote: > Here's an example of what I'm trying to do with my query > t_x > rowid=1,pos=1, txt=1990 > rowid=2,pos=3, txt=1992 > rowid=3,pos=2, txt=1991 > > t_y > rowid=1,pos=3,txt="sg&a expenses" > rowid=2,pos=2,txt="cogs" > rowid=3,pos=1,txt='revenue' > > t_d > rowid=1,xpos=1,ypos=1,d=$1 > rowid=2,xpos=1,ypos=2,d=$2 > rowid=3,xpos=1,ypos=3,d=$3 > rowid=4,xpos=3,ypos=1,d=$7 > rowid=5,xpos=3,ypos=2,d=$8 > rowid=6,xpos=3,ypos=3,d=$9 > rowid=7,xpos=2,ypos=1,d=$4 > rowid=8,xpos=2,ypos=2,d=$5 > rowid=9,xpos=2,ypos=3,d=$6 > > > So in the GUI you'd see t_x as 1990...1991,,,1992 cos that's pos order > > you'd see t_y as > revenue > cogs > sg&a expenses > cos that's pos order > > and you'd see t_d as > 1990 1991 1992 > revenue 1 2 3 > cogs 4 5 6 > sg&a 7 8 9 > > ie the order in which rows are added i.e. rowid order is not the order > of the row's position in the GUI (pos order is the GUI order) > > The query is to return those data cells encapsulated by the margin text > values so... > Select all cells BETWEEN 1990 and 1991 and cogs and sg&a. > The answer would be 4 5 7 8. > > Does this help to visualise what I'm trying to do? > > > > > On 1 July 2011 18:14, Pavel Ivanov <paiva...@gmail.com> wrote: > >> > 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 >> > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
