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
