sql - How can I select the set of rows where each item has the greatest timestamp?

Question

Using Sqlite, I'd like to fetch the collection of rows each with the greatest timestamp. The table contains the properties of items, which are key-value pairs and timestamp. I'd like to select the most recent value for each property.

Consider the following simplified schema and data:

CREATE TABLE Properties (thing VARCHAR,
                         key VARCHAR,
                         value VARCHAR,
                         timestamp INT);
INSERT INTO Properties VALUES ("apple", "color", "red", 0);
INSERT INTO Properties VALUES ("apple", "taste", "sweet", 0);
INSERT INTO Properties VALUES ("apple", "size", "small", 0);
INSERT INTO Properties VALUES ("watermelon", "taste", "sweet", 0);
INSERT INTO Properties VALUES ("watermelon", "size", "large", 0);
INSERT INTO Properties VALUES ("watermelon", "color", "pink", 1);
INSERT INTO Properties VALUES ("watermelon", "color", "green", 0);

I'd like to write a query for thing="watermelon" that returns:

taste|sweet
size|large
color|pink

Note that there are two rows with key="color", and the query returns the row with the greatest timestamp value. Also, the greatest timestamp for one property may be different from another property.

What I've tried so far includes:

Get the set of properties for thing="watermelon":

SELECT DISTINCT(key) FROM Properties WHERE thing='watermelon';

Get the most recent value of key="color" for thing="watermelon":

SELECT * 
FROM Properties
WHERE thing='watermelon'
  AND key='color'
ORDER BY timestamp DESC
LIMIT 1;

But I can't figure out how to combine the two. I'm probably coming at this from an imperative programming perspective, which is why I'd appreciate assistance.

Answer 1

In SQLite 3.7.11 or later, you can simply use MAX() to select one row from a group:

SELECT key, value, MAX(timestamp)
FROM Properties
WHERE thing = 'watermelon'
GROUP BY key;