> 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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 >>> <[email protected]> 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 <[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 >>> > >>> _______________________________________________ >>> 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
