select last change Row for each group SQLITE

Question

I have table with 4 car registrations and some data for each of them and I need to select the latest changed row for each registration with SQLite, could you help me?

Kind regards Philippe

Answer 1

If your latest changes were made with your current connection open, you can use the last_insert_rowid() function for inserts or your whereClause for updates as explained in this post.

If you've already closed your connection or want to select the latest changed rows at any time other than right after making the changes, you'll have to make the database remember which row was changed last. Here's an example using timestamps:

Let's consider the following schema.

CREATE TABLE car (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  content TEXT,
  last_change TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Now last_change will be set to the creation time when a new row is inserted.

We still need to update last_change on every UPDATE query, so let's create a trigger for that.

CREATE TRIGGER update_last_change
  AFTER UPDATE
  ON car
  BEGIN
    UPDATE car SET last_change = CURRENT_TIMESTAMP WHERE id = NEW.id;
  END;

You can use the following query to get the row with the latest change.

SELECT * FROM car ORDER BY last_change DESC LIMIT 1;

This will always return at most one row. There can be multiple rows with the same last_change. If you want all rows with the latest last_change in such a case, use the following query.

SELECT * FROM car WHERE last_change = (
  SELECT last_change FROM car ORDER BY last_change DESC LIMIT 1
);