In SQLite, select single most recent row for each foreign key

Question

In SQLite, tables x and y are defined as follows:

CREATE TABLE x (
    x_pk INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
);

CREATE TABLE y (
    y_pk INTEGER PRIMARY KEY AUTOINCREMENT,
    x_fk INTEGER,
    rating REAL,
    date default current_timestamp,
    FOREIGN KEY (x_fk) REFERENCES x (x_pk)
);

Table y provides a log of past ratings, but should also provide the latest rating of each item in table x. I can get the latest rating of a single item (e.g. x_fk=8) using: SELECT * FROM y WHERE x_fk=8 ORDER BY date LIMIT 1;, but how can I select the single most recent rows for every distinct x_fk in table y?

Could you provide some data and result expected? BTW, using this kind of nomenclature will only brings problems than solutions. — Leandro Bardelli, Mar 16 '23 at 23:39
I thought it was obvious enough, but I'll edit to provide an example if needed after I check out the answer that has already been posted. Maybe an explanation would suffice. You could insert any data into the "name" field of table x and the "rating" field of table y. There is a one-to-many relationship between tables x and y, and there would be duplicates in field x_fk of table y as the latest ratings are logged. The output (e.g. "latest_ratings" view) should be the most recent rows of table y (according to the date field) with no duplicates in the foreign key field. — Ivo Treszka, Mar 17 '23 at 01:32
Does this answer your question? [Fetch the rows which have the Max value for a column for each distinct value of another column](https://stackoverflow.com/questions/121387/fetch-the-rows-which-have-the-max-value-for-a-column-for-each-distinct-value-of) — philipxy, Mar 17 '23 at 02:04
It is a similar except the question was for Oracle rather than SQLite. I found similar questions for MySQL and PostgreSQL before posting and they used functions I thought might not be available in SQLite, but the `MAX()` in SQLite does work the same on timestamp fields, so yes, I think the selected answer for Oracle in your link can be adapted to answer my question. — Ivo Treszka, Mar 17 '23 at 03:53

nbk · Accepted Answer · 2023-03-17T00:55:16.147

If you can't have duplicate dates for every x_fk, the first query work work, But if you have duplicate, the second one will provide a solution

CREATE TABLE y (
    y_pk INTEGER PRIMARY KEY AUTOINCREMENT,
    x_fk INTEGER,
    rating REAL,
    date default current_timestamp
);

✓``` INSERT INTO y (x_fk ,rating) VALUEs(1,1),(1,2),(1,3),(2,1),(2,2)

✓``` 
SELECT y.x_fk, rating
  , ROW_NUMBER() OVER (PARTITION BY y.x_fk ORDER BY y.date DESC, rating DESC) rn FROM y

x_fk	rating	rn
1	3	1
1	2	2
1	1	3
2	2	1
2	1	2

SELECT y.x_fk, rating FROM y JOIN
(SELECT MAX(date) as Max_date,x_fk FROM y
  GROUP By x_fk) y1 ON y.x_fk = y1.x_fk And y.date = y1.max_date

x_fk	rating
1	1
1	2
1	3
2	1
2	2

WITH CTE AS (
SELECT y.x_fk, rating, ROW_NUMBER() OVER (PARTITION BY y.x_fk ORDER BY y.date DESC, rating DESC) rn FROM y JOIN
(SELECT MAX(date) as Max_date,x_fk FROM y
  GROUP By x_fk) y1 ON y.x_fk = y1.x_fk And y.date = y1.max_date)
SELECT x_fk, rating FROM CTE WHERE rn = 1

x_fk	rating
1	3
2	2

fiddle

Thank you. These are solutions I can learn from. The date is supposed to be unique, and I assumed it would be given that current_timestamp is per second, but your comment made me realise that I should include a unique constraint for the date field in case of accidental duplicates in a single insert. — Ivo Treszka, Mar 17 '23 at 02:23

In SQLite, select single most recent row for each foreign key

1 Answers1