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]
