On Tue, Feb 24, 2009 at 3:16 AM, Mail.sqlite <mail.sql...@imtas.de> wrote:
>
> Hi All,
> I searched trough many messages and docs but did not find a solution to this
> trivial looking problem. It would be really nice if someone could point me
> into the right direction.
>
> my ToDo:
> I have to use a virtual listview for a database with some medium and large
> tables. I would give the user the opportunity to select the active displayed
> sorting order with a click on the corresponding listview header.
> To get the necessary number of records for the table with high performance,
> even for large files, this can be done with a trigger that updates a record
> elsewhere with every delete or insert operation.
>
> My Problem:
> how to get the relative position for the selected row depending on the
> selected “order by” clause as fast as possible? An estimate should be enough
> to position the scrollbar.
>
If you had sequential IDs of some sort in your returned result set,
this would be a trivial problem.
Of course, you are likely to not have a continuous sequence in a
database table because some rows may have been deleted. But, you can
fool it by creating a temp table to store your result set... consider
the following --
sqlite> create table foo (a integer primary key, b text);
sqlite> insert into foo (b) values ('blah');
sqlite> insert into foo (b) values ('grop');
sqlite> insert into foo (b) values ('farc');
sqlite> insert into foo (b) values ('drat');
sqlite> select rowid, a, b from foo;
1|1|blah
2|2|grop
3|3|farc
4|4|drat
sqlite> delete from foo where a = 3;
sqlite> select rowid, a, b from foo;
1|1|blah
2|2|grop
4|4|drat
sqlite> create temp table bar as select a, b from foo;
sqlite> select rowid, a, b from bar;
1|1|blah
2|2|grop
3|4|drat
sqlite>
Now we are back to where your problem is trivial. You can use a
rowid/count(*) value to determine the "position" of a row in an
ordered set.
> Thanks for any hints.
>
> George
>
--
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
Sent from: Madison WI United States.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
