SQLite 3.39

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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