CODEWITHSUNDEEP
sqlsqlitegreatest-n-per-group
Willi Ballenthin
Willi Ballenthin

Reputation: 6604

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

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.

Upvotes: 1

Views: 242

Answers (3)

CL.
CL.

Reputation: 180070

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;

Upvotes: 4

Lukasz Szozda
Lukasz Szozda

Reputation: 175786

Use HAVING for simple and readable solution:

SQLFiddleDemo

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

Upvotes: 0

Willi Ballenthin
Willi Ballenthin

Reputation: 6604

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.

Upvotes: 1

Related Questions