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