INNER JOINでサブテーブルの最大レコードをくっ付ける

メインテーブルに関連付くサブテーブルのうち
サブのIDが最大の情報を取ってくる場合のSQL

select a.*,c.* from main_table a
inner join 
(
 select main_id,max(sub_id) sub_id
 from 
 sub_table
 group by main_id
) b on a.main_id = b.main_id
inner join sub_table c on c.sub_id = b.sub_id
;

テーブルとINSERT文

CREATE TABLE "main_table" (
"main_id" INTEGER,
"main_info" TEXT
);

CREATE TABLE "sub_table" (
"main_id" INTEGER,
"sub_id" INTEGER,
"write_date" INTEGER,
"sub_info" TEXT
);

insert into main_table values (1,"main_1");
insert into main_table values (2,"main_2");

insert into sub_table values(1,1,20220101,"sub_1_1");
insert into sub_table values(1,2,20220102,"sub_1_2");
insert into sub_table values(1,3,20220103,"sub_1_3");

insert into sub_table values(2,4,20220104,"sub_2_1");
insert into sub_table values(2,5,20220105,"sub_2_2");
insert into sub_table values(2,6,20220106,"sub_2_3");