Get records for the latest timestamp in SQLite

Question

I know this question has been asked before by others but all those questions use either MySQL or other databases. How to do in SQLite? I am asking since SQLite doesn't have a separate date data type. I have created the table with this schema

Fragment:

CREATE TABLE x (
id INTEGER NOT NULL,
timestamp TIMESTAMP NOT NULL,
PRIMARY KEY(timestamp,id)
);

Will something like this work?

SELECT * FROM x
WHERE DATE(timestamp) = (
    SELECT MAX(DATE(timestamp)) FROM x
)

Solution

Timestamps like 2014-03-05T20:00:00.000 have the property that the lexicographic (alphabetical) ordering is the same as chronological ordering and you can use plain old comparison operators and ORDER BY on them, like this:

SELECT * FROM x ORDER BY timestamp DESC LIMIT 1;

Comments

[pratnala]

Sorry for the trouble. It will be like this : 2014-03-05 20:00:00.000 Basically, instead of the T, there is a space. Will this still hold?

[laalto]

Yes, it doesn't matter as long as the components are in descending magnitude (yyyy-MM-dd HH:mm:ss.zzz fits the pattern).

[pratnala]

Thanks a ton! Just what I needed

[pratnala]

Another doubt! How to edit this so that I get the latest timestamped record for each id? Each id can have many records with different timestamps. The number of ids is finite (a small number)

[vapcguy]

You need to settle on ONE timestamp to do your ORDER BY by. If you have several dates because, say, it's an item that gets ordered on one date, processed on another, delivered on another, either settle on just one type of date, or don't even use dates-use an ID or order number. If it doesn't have one, add an auto-increment column to your table. Example, say I originally order by Order Date, then Delivery Date. I'm not sure I see value in re-ranking something lower if it's ordered one day earlier than another, but then delivered later, if I wanted to go by Delivery Date in the first place.