Pavel and David... I just moved some of the table insertions around to change their rowid values and the results are STILL coming out in pos order so... which wasn't what I was getting with my attempts so... Thank you very much indeed for your advice and solution. It is appreciated!
On 1 July 2011 18:41, e-mail mgbg25171 <mgbg25...@blueyonder.co.uk> wrote: > > If you believe that > result of a query differs depending on what order SQLite processes > rows in then you are wrong. > > I am wrong! > > > On 1 July 2011 18:38, e-mail mgbg25171 <mgbg25...@blueyonder.co.uk> wrote: > >> 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
