Hi, again I've figured out an inconsistency with subselects (and again I think it's sqlite's fault... ;)
SQLite version 2.8.12 Enter ".help" for instructions sqlite> .echo on sqlite> .read test.sql .read test.sql DROP VIEW t1view; DROP VIEW t1view2; DROP TABLE t1; CREATE TABLE t1 ( id INTEGER PRIMARY KEY, name VARCHAR(10), date VARCHAR(10), info VARCHAR(20) ); INSERT INTO t1 VALUES ( NULL, 'first', '2004-01-20', 'first''s first info' ); INSERT INTO t1 VALUES ( NULL, 'second', '2004-02-12', 'second''s first info' ); INSERT INTO t1 VALUES ( NULL, 'first', '2004-02-14' , 'first''s second info' ); -- show me the most current entries per name CREATE VIEW t1view AS SELECT b.* FROM ( SELECT name, max(date) AS date FROM t1 GROUP BY name ) AS a LEFT JOIN t1 AS b ON (a.name=b.name AND a.date=b.date) ; -- SELECT works ok: SELECT * FROM t1view; id name date info ---------- ---------- ---------- ------------------- 2 second 2004-02-12 second's first info 3 first 2004-02-14 first's second info -- SELECT WHERE is wrong: SELECT * FROM t1view WHERE id=3; id name date info ---------- ---------- ---------- ---------- NULL NULL NULL NULL 3 first 2004-02-14 first's se -- the previous view with reordered tables CREATE VIEW t1view2 AS SELECT b.* FROM t1 AS b LEFT JOIN ( SELECT name, max(date) AS date FROM t1 GROUP BY name ) AS a ON (a.name=b.name AND a.date=b.date) WHERE a.name NOT NULL ; -- SELECT works ok: SELECT * FROM t1view2; id name date info ---------- ---------- ---------- ------------------- 2 second 2004-02-12 second's first info 3 first 2004-02-14 first's second info -- SELECT WHERE works ok: SELECT * FROM t1view2 WHERE id=3; id name date info ---------- ---------- ---------- ------------------- 3 first 2004-02-14 first's second info sqlite> --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]