Hello there, I've found the implementation-recomendations for scrolling windows in sqlite [1]. We've currently implemented it with LIMIT and OFFSET as described in section "What Not To Do" ;-)
The solution described in "Work Arounds" seems really faster than our implementation... but can I implement it also when there's a unique constraint on multiple columns? Here an example: create table device( id integer primary key, manufacturer VARCHAR(200) NOT NULL, model_name VARCHAR(200) NOT NULL, CONSTRAINT U_device UNIQUE ( manufacturer, model_name ) ) ; INSERT INTO device (id,manufacturer,model_name) VALUES (NULL,"M2","X1000"); INSERT INTO device (id,manufacturer,model_name) VALUES (NULL,"M2","X500"); INSERT INTO device (id,manufacturer,model_name) VALUES (NULL,"M1","X1000"); INSERT INTO device (id,manufacturer,model_name) VALUES (NULL,"M2","A5"); INSERT INTO device (id,manufacturer,model_name) VALUES (NULL,"M3","TX200"); INSERT INTO device (id,manufacturer,model_name) VALUES (NULL,"M3","A1"); INSERT INTO device (id,manufacturer,model_name) VALUES (NULL,"M3","X1000"); -- Statement for whole list: SELECT manufacturer, model_name FROM device ORDER BY manufacturer; -- Statement sent by application for the second scrolling step (should show row 4 to 6): SELECT manufacturer, model_name FROM device WHERE manufacturer > 'M2' ORDER BY manufacturer LIMIT 3; The first problem is that the second page should start with ( M2 | X500 ) but the where clause causes that all rows with manufacturer="M2" are omitted. If I use two columns in the where-clause the output still gets worse: ...WHERE manufacturer > 'M2' AND model_name > 'X1000'... also omitts the row ( M3, X1000 ). Is there a way to speed up my scrolling-window queries for tables in that style? Thanks in advance. Best Regards, Dieter Guthmann [1] <http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
