B V, Phanisekhar <[EMAIL PROTECTED]> wrote:
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.
You must realize that rows in a SQL table cannot be said to be in any particular order, until a SELECT statement with an ORDER BY clause imposes one. A table is a relation - an unordered set of tuples. That's why it's called a _relational_ database. Any apparent order you may observe when issuing a SELECT without ORDER BY is an implementation detail and should not be relied on.
So, if you want to model files stored in a folder in a particular sequence, you must explicitly maintain this sequence, perhaps with an additional column storing a sequence number. In SQLite, INTEGER PRIMARY KEY AUTOINCREMENT column may be convenient for this purpose.
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)
select rowid from maintable m join relationtable r on (m.puid = r.puid) where r.AbsPuid = 7 order by r.sequence;
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
That depends greatly on which indexes you have defined on your tables, if any. If you want to know for sure, run your query prepending an EXPLAIN keyword to it. The output is a query plan - a set of virtual machine instructions SQLite follows to execute the query. See http://sqlite.org/opcode.html .
Igor Tandetnik
----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
