I have a bunch of uris stored in a table
CREATE TABLE uris (
uri_id INTEGER PRIMARY KEY,
uri TEXT
);
uri_id uri
---------- --------------
1 http://foo.com
2 http://bar.com
3 http://baz.com
4 http://qux.com
A program periodically downloads the content of the above web sites. Another
table stores the download history, that is, when the content was last
downloaded.
CREATE TABLE history (
history_id INTEGER PRIMARY KEY,
uri_id INTEGER,
downloaded_on DATETIME DEFAULT CURRENT_TIMESTAMP
);
history_id uri_id downloaded_on
---------- ---------- -------------------
1 4 2011-05-04 02:25:09
2 3 2011-05-03 02:34:08
3 1 2011-05-01 02:50:43
4 2 2011-05-02 02:50:45
6 4 2011-05-14 02:50:48
The content itself is stored in an FTS4 table linked by history_id.
CREATE VIRTUAL TABLE fts_uri (
history_id, content
);
I am looking for an efficient way to select the uris, and the latest
'downloaded_on' time stamp for each uri, but am drawing a blank. The list
should look like so
uri_id uri downloaded_on
---------- -------------- ---------------
1 http://foo.com 2011-05-01 02:50:43
2 http://bar.com 2011-05-02 02:50:45
3 http://baz.com 2011-05-03 02:34:08
4 http://qux.com 2011-05-14 02:50:48
Suggestions?
Puneet.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
