Part of the problem here is that you are misusing the term BETWEEN. In SQL txt
BETWEEN 1990 AND 1991 means those rows where the value of txt is >= 1990 AND <=
1991. It has nothing to do with the order of the rows in the table, it is
purely a comparison of the value of txt in a particular row and the minimum and
maximum value given in the BETWEEN statement.
Without testing it, I guess your query needs to be:
SELECT d from T_d WHERE
xpos BETWEEN (SELECT pos FROM T_x WHERE txt = '1990')
AND (SELECT pos FROM T_x WHERE txt = '1991')
AND
ypos BETWEEN (SELECT pos FROM T_y WHERE txt = 'cogs')
AND (SELECT pos FROM T_y WHERE txt = 'sg&a expenses');
David
--- On Fri, 7/1/11, e-mail mgbg25171 <mgbg25...@blueyonder.co.uk> wrote:
> From: e-mail mgbg25171 <mgbg25...@blueyonder.co.uk>
> Subject: Re: [sqlite] Ensure that query acts on PRE-SORTED tables
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> Date: Friday, July 1, 2011, 1:38 PM
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
