I have a database using sqlite3 and one of the columns is storing a date (rfc3339 format) as TEXT.

My database is storing daily values for various objects. In the example below, A and B.

What I want to do is to select just the newest date for each object. The objects are referenced in another database. The desired result would be:

If I have hundreds of entries for the obj A, B, C... how can I select just the newest one for each object? (that is, grouped by the value on the column obj)

CodePudding user response：

You can use ROW_NUMBER() function such as

SELECT id, obj, date, value
  FROM ( SELECT t.*,
               ROW_NUMBER() OVER 
               (PARTITION BY obj 
                    ORDER BY strftime('%Y-%m-%dT%H:%M:%S.%fZ', date) DESC) AS rn 
           FROM t ) AS tt 
 WHERE rn = 1

CodePudding user response：

With simple aggregation:

SELECT id, obj, MAX(date) AS date, value
FROM tablename
GROUP BY obj;

SQLite returns for each obj the row with the most recent date.
For reference: Bare columns in an aggregate query