MySQL - Find the latest version of the First Child-CodePudding

I have a Parent object that has many Versions.

CREATE TABLE version (
    id int,
    item_id int,
    logged_at datetime,
    parent_id int
);

CREATE TABLE parent (
  id int
);

INSERT INTO version (id, item_id, logged_at, parent_id)
VALUES (1, 1, "2021-12-01 20:00:00", 1);
INSERT INTO version (id, item_id, logged_at, parent_id)
VALUES (2, 1, "2021-12-01 20:20:00", 1);
INSERT INTO version (id, item_id, logged_at, parent_id)
VALUES (3, 1, "2021-12-01 21:40:00", 1);
INSERT INTO version (id, item_id, logged_at, parent_id)
VALUES (4, 2, "2021-12-01 21:40:00", 1);
INSERT INTO version (id, item_id, logged_at, parent_id)
VALUES (5, 2, "2021-12-01 20:20:00", 1);

INSERT INTO parent (id)
VALUES (1);

As you can see, each Version belongs to different Items. Where the only thing differentiating them is the .item_id they belong to and the .logged_at attribute.

My objective is to find the lowest .item_id Version with the latest .logged_at.

Thus the desired result should in this example be :

VALUES (3, 1, "2021-12-01 21:40:00", 1);

I understand row_number() partitions can acquire the first child, but I'm having difficulty understanding how to do a secondary faceted search.

Any help or pointers would be greatly appreciated. Thank you!

CodePudding user response：

you can try it like this.

SELECT *, ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY logged_at DESC) row_num
FROM VERSION
ORDER BY logged_at DESC, row_num, item_id
LIMIT 1

Sort by item_id according to logged_in time by PARTITION BY syntax.

Then, for items with the same item_id, the ranking is determined according to the logged_in.

From that table, order by logged_in desc is the primary to know the most recently logged in user, and order by row_num, item_id are secondary to select the lowest item_id among users who logged in at the same time.