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.

score 4 · Answer 1 · answered Sep 13 '15 at 07:23

4

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;

answered Sep 13 '15 at 07:23

CL.

173,858
17
217
259

1

How does SQLite know to pick the `value` that corresponds to the max timestamp? What if the query was `select key, value, max(timestamp), min(timestamp)`, which `value` would the query return then? – FuzzyTree Sep 13 '15 at 13:42
Then it randomly picks one of those two rows. – CL. Sep 13 '15 at 13:49
1

wouldn't it still pick a random `value` row in your query? I'm not sure about SQLite but this is a common mistake in MySQL because according to the MySQL documentation the engine is permitted to return a random value when selecting a column not in the group by but in practice it often returns the one associated with the max/min etc, so people assume that's how it works. But the behaviour could change version by version. http://stackoverflow.com/questions/1752556/is-it-safe-to-include-extra-columns-in-the-select-list-of-a-sqlite-group-by-quer – FuzzyTree Sep 13 '15 at 13:57
1

SQLite guarantees that the other columns' values come from a row that matches a MIN()/MAX(). – CL. Sep 13 '15 at 13:57
[From the official docs](https://www.sqlite.org/lang_select.html) If the expression is an aggregate expression, it is evaluated across all rows in the group. Otherwise, it is evaluated against a single **arbitrarily** chosen row from within the group. – FuzzyTree Sep 13 '15 at 14:09
2

When there is MIN()/MAX(), the choice [is not arbitrary](http://www.sqlite.org/releaselog/3_7_11.html). – CL. Sep 13 '15 at 21:12

score 1 · Answer 2 · edited May 23 '17 at 12:23

1

Tweaking the query found here, I've come up with the following:

SELECT a.* 
FROM Properties AS a 
INNER JOIN (
  SELECT key, MAX(timestamp) AS max_timestamp 
  FROM Properties 
  WHERE thing='watermelon' 
  GROUP BY key) b 
ON a.key = b.key AND a.timestamp = b.max_timestamp 
WHERE thing='watermelon';

Seems to work, though I'd be interested in comments the pros/cons of this query.

edited May 23 '17 at 12:23

Community

1
1

answered Sep 12 '15 at 16:17

Willi Ballenthin

6,444
6
38
52

Lukasz Szozda · Answer 3 · 2015-09-12T17:57:08.420

0

Use HAVING for simple and readable solution:

SQLFiddleDemo

SELECT *
FROM Properties
WHERE thing = "watermelon"
GROUP BY thing, key
HAVING timestamp = MAX(timestamp)

edited Sep 12 '15 at 17:57

answered Sep 12 '15 at 17:48

Lukasz Szozda

162,964
23
234
275

Any particular reason for downvote? If this solution is somehow wrong I will be happy to hear it. – Lukasz Szozda May 11 '16 at 15:28

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

3 Answers3

Linked

Related