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
