Thank you Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
e-mail mgbg25171 wrote:
> I know that ORDER BY sorts result but I want to sort a table BEFORE it gets
> queried and am not sure of the syntax.
> Here's my test program but...I'm not sure how to PRE-SORT tables t_x and t_y
> (by column pos) BEFORE I do the SELECT BETWEEN on THEM
> i.e. I purposeful
The between operator is order dependent. This variation might work:
SELECT d from T_d
inner join (select min(pos) as xMin, max(pos) as yMax FROM T_x WHERE txt
= '1990' OR txt='1991') as xcriteria on xPos between xMin and xMax
inner join (select min(pos) as yMin, max(pos) as yMax FROM T_y WHERE t
ECT 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 wrote:
> From: e-mail mgbg25171
> Subject: Re: [sqlite] Ensure that query acts on PRE-SORTED tables
> To: "General
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
> 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 wrote:
> Just to clarify further "pos BETWEEN txt = 1990 and 1991" as its stands
> looks (to my naive eye) like its going to re
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
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,
> 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 fro
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 intereste
> 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
u what you want:
SELECT POS FROM T_x WHERE POS BETWEEN
(SELECT POS FROM T_x WHERE txt = 'x1')
AND
(SELECT POS FROM T_x WHERE txt = 'x2');
Hopefully I have guessed your need somewhat correctly,
David
--- On Fri, 7/1/11, e-mail mgbg25171 wrote:
> From: e-mail mgbg2
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
> 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 s
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
> 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
wrote:
> Thank you all
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 spreadshe
>> 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
writte
On 1 Jul 2011, at 3:07pm, Alessandro Marzocchi wrote:
> 2011/7/1 Simon Slavin
>
>> 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 s
2011/7/1 Simon Slavin
>
> 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 execute
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.
Simon.
2011/7/1 Mohit Sindhwani
> On 1/7/2011 5:37 PM, Martin.Engelschalk wrote:
> > Hi,
> >
> > i apologize beforehand if my post does not answer your question
> > directly. It seems to me that you may be missing a basic concept.
> >
> > Data in an SQL table is never sorted in itself. So, you can not s
On 1/7/2011 5:37 PM, Martin.Engelschalk wrote:
> Hi,
>
> i apologize beforehand if my post does not answer your question
> directly. It seems to me that you may be missing a basic concept.
>
> Data in an SQL table is never sorted in itself. So, you can not sort a
> table before you query it.
SQLit
Hi,
i apologize beforehand if my post does not answer your question
directly. It seems to me that you may be missing a basic concept.
Data in an SQL table is never sorted in itself. So, you can not sort a
table before you query it.
If you select data without an "order by" - clause, the order i
I know that ORDER BY sorts result but I want to sort a table BEFORE it gets
queried and am not sure of the syntax.
Here's my test program but...I'm not sure how to PRE-SORT tables t_x and t_y
(by column pos) BEFORE I do the SELECT BETWEEN on THEM
i.e. I purposefully inserted t_d row 1,1,1 to see i
25 matches
Mail list logo