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] -
[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] -