Re: [sqlite] selecting unique list of latest timestamps

[Igor Tandetnik]

Mr. Puneet Kishor  wrote:
> CREATE TABLE uris (
> uri_id INTEGER PRIMARY KEY,
> uri TEXT
> );
> 
> 
> CREATE TABLE history (
> history_id INTEGER PRIMARY KEY,
> uri_id INTEGER,
> downloaded_on DATETIME DEFAULT CURRENT_TIMESTAMP
> );
>
> 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.

select uris.uri_id, uris.uri, max(downloaded_on)
from uris join history using (uri_id)
group by uris.uri_id;

-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] selecting unique list of latest timestamps

[Mr. Puneet Kishor]

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