JP <[EMAIL PROTECTED]> wrote:
> SQLite provides a way to get the N-th row given a SQL statement, with
> LIMIT 1 and OFFSET <N>.
>
> Can the reverse be done in an efficient way? For example, given a table
> with 1million names, how can I return the row number for a particular
> element?
No, you can't do that in SQL. The results of an SQL query
are a set of rows; the rows are not produced in any
guaranteed order. The set is converted to an ordered list
only by a sort (order by) after all the result rows are
retrieved.
If I understand correctly what you want to do, you'll have
to retrieve all the rows into an ordered collection, then
search for the name you're interested in. An alternative
would be to add a column RowNum and update it every time
you insert a row.
newRowNum <- 1 + {select max(RowNum) from t where Name < 'foo'} or 0
update t set RowNum = RowNum + 1 where RowNum >= newRowNum
insert into t (Name, RowNum, ...) values ('foo', newRowNum, ...)
Regards
