A self-join query on the Master/Slave table

Two tables master table userinfo associate one-to-many relationships with userid from the postinfo table
Userinfo table
Userid Username

1	Jecray
2	Yang
3	Chen
4	YC
Null	Null

Postinfo table
Postid | title | userid | content | order

1	Title1	1	Content1	5
2	Title2	2	Content2	4
3	Title3	1	Content3	2
4	Title4	1	Content4	1
5	Title5	3	Content5	5
6	Title6	3	Content6	8
7	Title7	4	Content7	1
8	Title8	2	Content8	2
9	Title9	2	Content9	8
Null	Null	Null	Null	Null

Requirement: use an SQL statement to obtain the record with the largest orderid value of post for each user.
If orderid is time, it is used to obtain the relevant information of each user posting the latest post. This information includes the information in the two tables.

Expected results:
Postid title userid content orderid Username
1 title1 1 content1 5 jecray
6 title6 3 content6 8 Chen
7 title7 4 content7 1 YC
9 title9 2 content9 8 Yang

Take two steps:
First, the records with the largest orderid value in postinfo are obtained through SQL self-join. Select distinct .*
From postinfo A where a. postid in
(Select top 1 postinfo. postid
From postinfo
Where postinfo. userid = A. userid
Order by orderid DESC)

Match the record with the specified condition in the search of each row. If you replace the record with top 2, you can retrieve the record with the largest orderid in the first two orders.

Connect userinfo and postinfo. Select distinct A. *, B .*
From postinfo a inner join userinfo B on A. postid in
(Select top 1 postinfo. postid
From postinfo
Where postinfo. userid = A. userid
Order by orderid DESC) and A. userid = B. userid

The above results can be obtained. The self-connection efficiency is not very good, and it will not be very fast.

If it is in Oracle, it can be implemented in this way. Select * From postinfo where (uerid, orderid) in (select userid, max (orderid) as orderid from postinfo group by userid)

Unfortunately, SQL Server cannot pass

As long as there is a logical relationship between the same field in a table or between different fields, you may use a self-join query.