Re: [sqlite] Re: Re: Order of result of a query?
B V, Phanisekhar wrote: Kennedy, You are using Join that's going to take time. I need the most efficient query. Dan's one of the SQLite developers, so presumably has a rough idea of what his suggested query will be doing... ;) Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Re: Order of result of a query?
Kennedy,
You are using Join that's going to take time. I need the most
efficient query.
Regards,
Phanisekhar
-Original Message-
From: Dan Kennedy [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 16, 2007 12:54 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Re: Re: Order of result of a query?
On Wed, 2007-05-16 at 11:39 +0530, B V, Phanisekhar wrote:
> Igor,
>
> Assume I have a database of the files/folders.
>
> Let it be
>
> Rowid puid
> 1 1
> 2 2
> 3 3
> 4 5
> 5 7
> 6 8
> 7 10
>
> Assume I have a relation table showing which file is in which folder
>
> AbsPuid Puid
> 710
> 72
> 78
> 75
>
> The above relation table tells that the object 10, 2, 8, 5 is inside a
> folder object 7.
>
> Now assume the entries are added to this relation ship table in the
> sequence in which the files are created inside the folder along with
the
> sequence by which they were moved inside the folder. So the above
table
> says file 10 was first added to the sequence then 2, then 8, and at
last
> 5. Now assume I want to retrieve the rowid information in the order in
> which the objects have been added to the folder. I am using the
> following SQL query:
>
> Select rowid from maintable where puid in (select puid from
> relationtable where AbsPuid =7)
>
> The subquery will return the values {10, 2, 8, 5} but the result of
the
> main query is {2, 4, 6, 7} rather than {7, 2, 6, 4}. So what should be
> the query so that my end result is {7, 2, 6, 4}
SELECT maintable.rowid
FROM maintable, relationtable
WHERE AbsPuid=7 AND maintable.puid=relationtable.puid
ORDER BY relationtable.rowid
Or something like that anyhow.
Dan.
> I hope this will clear the question.
>
> Another doubt which I have is will the SQLite search the entire table
> with the first entry in the result set then followed by second entry
in
> the result set, and so on or will it first check the first row against
> all the values in the result test, then second row with all the values
> in the result set and so on. If the first case the result will be {7,
2,
> 6, 4} and in the latter the result will be {2, 4, 6, 7}. Hence I feel
> that sqlite follows the second case. So is that true?
>
>
> Chris,
> I don't have any specific precise display sequence to mention it
using
> another column.
>
>
> Regards,
> Phanisekhar
>
>
>
>
> -Original Message-
> From: Igor Tandetnik [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 15, 2007 8:13 PM
> To: SQLite
> Subject: [sqlite] Re: Re: Order of result of a query?
>
> B V, Phanisekhar <[EMAIL PROTECTED]> wrote:
> > Assume the values in the OR clause, be replaced by some subquery.
Then
> > in such scenarios how will I be able to maintain the order?
> > I want the order of the subquery to be preserved in the main query.
>
> Show an example. I'd like to look at the ORDER BY clause of that
> subquery (it does have one, right)? Then I'll essentially move the
ORDER
>
> BY out of the subquery and into the main query.
>
> > For e.g.: for the sub query returned values in order (2, 8, 7, 3)
>
> How does the subquery impose this particular order?
>
> Igor Tandetnik
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>
>
-
> To unsubscribe, send email to [EMAIL PROTECTED]
>
-
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Re: Re: Order of result of a query?
On Wed, 2007-05-16 at 11:39 +0530, B V, Phanisekhar wrote:
> Igor,
>
> Assume I have a database of the files/folders.
>
> Let it be
>
> Rowid puid
> 1 1
> 2 2
> 3 3
> 4 5
> 5 7
> 6 8
> 7 10
>
> Assume I have a relation table showing which file is in which folder
>
> AbsPuid Puid
> 710
> 72
> 78
> 75
>
> The above relation table tells that the object 10, 2, 8, 5 is inside a
> folder object 7.
>
> Now assume the entries are added to this relation ship table in the
> sequence in which the files are created inside the folder along with the
> sequence by which they were moved inside the folder. So the above table
> says file 10 was first added to the sequence then 2, then 8, and at last
> 5. Now assume I want to retrieve the rowid information in the order in
> which the objects have been added to the folder. I am using the
> following SQL query:
>
> Select rowid from maintable where puid in (select puid from
> relationtable where AbsPuid =7)
>
> The subquery will return the values {10, 2, 8, 5} but the result of the
> main query is {2, 4, 6, 7} rather than {7, 2, 6, 4}. So what should be
> the query so that my end result is {7, 2, 6, 4}
SELECT maintable.rowid
FROM maintable, relationtable
WHERE AbsPuid=7 AND maintable.puid=relationtable.puid
ORDER BY relationtable.rowid
Or something like that anyhow.
Dan.
> I hope this will clear the question.
>
> Another doubt which I have is will the SQLite search the entire table
> with the first entry in the result set then followed by second entry in
> the result set, and so on or will it first check the first row against
> all the values in the result test, then second row with all the values
> in the result set and so on. If the first case the result will be {7, 2,
> 6, 4} and in the latter the result will be {2, 4, 6, 7}. Hence I feel
> that sqlite follows the second case. So is that true?
>
>
> Chris,
> I don't have any specific precise display sequence to mention it using
> another column.
>
>
> Regards,
> Phanisekhar
>
>
>
>
> -Original Message-
> From: Igor Tandetnik [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 15, 2007 8:13 PM
> To: SQLite
> Subject: [sqlite] Re: Re: Order of result of a query?
>
> B V, Phanisekhar <[EMAIL PROTECTED]> wrote:
> > Assume the values in the OR clause, be replaced by some subquery. Then
> > in such scenarios how will I be able to maintain the order?
> > I want the order of the subquery to be preserved in the main query.
>
> Show an example. I'd like to look at the ORDER BY clause of that
> subquery (it does have one, right)? Then I'll essentially move the ORDER
>
> BY out of the subquery and into the main query.
>
> > For e.g.: for the sub query returned values in order (2, 8, 7, 3)
>
> How does the subquery impose this particular order?
>
> Igor Tandetnik
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Re: Re: Order of result of a query?
Igor,
Assume I have a database of the files/folders.
Let it be
Rowid puid
1 1
2 2
3 3
4 5
5 7
6 8
7 10
Assume I have a relation table showing which file is in which folder
AbsPuid Puid
7 10
7 2
7 8
7 5
The above relation table tells that the object 10, 2, 8, 5 is inside a
folder object 7.
Now assume the entries are added to this relation ship table in the
sequence in which the files are created inside the folder along with the
sequence by which they were moved inside the folder. So the above table
says file 10 was first added to the sequence then 2, then 8, and at last
5. Now assume I want to retrieve the rowid information in the order in
which the objects have been added to the folder. I am using the
following SQL query:
Select rowid from maintable where puid in (select puid from
relationtable where AbsPuid =7)
The subquery will return the values {10, 2, 8, 5} but the result of the
main query is {2, 4, 6, 7} rather than {7, 2, 6, 4}. So what should be
the query so that my end result is {7, 2, 6, 4}
I hope this will clear the question.
Another doubt which I have is will the SQLite search the entire table
with the first entry in the result set then followed by second entry in
the result set, and so on or will it first check the first row against
all the values in the result test, then second row with all the values
in the result set and so on. If the first case the result will be {7, 2,
6, 4} and in the latter the result will be {2, 4, 6, 7}. Hence I feel
that sqlite follows the second case. So is that true?
Chris,
I don't have any specific precise display sequence to mention it using
another column.
Regards,
Phanisekhar
-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 15, 2007 8:13 PM
To: SQLite
Subject: [sqlite] Re: Re: Order of result of a query?
B V, Phanisekhar <[EMAIL PROTECTED]> wrote:
> Assume the values in the OR clause, be replaced by some subquery. Then
> in such scenarios how will I be able to maintain the order?
> I want the order of the subquery to be preserved in the main query.
Show an example. I'd like to look at the ORDER BY clause of that
subquery (it does have one, right)? Then I'll essentially move the ORDER
BY out of the subquery and into the main query.
> For e.g.: for the sub query returned values in order (2, 8, 7, 3)
How does the subquery impose this particular order?
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
