Group and Sort based on JOIN and 2 different columns

Question

Here is my current SQL code, which orders posts by created_at column:

    select users.name as user_name, * 
    from posts
    join users on users.id = posts.user_id
    order by posts.created_at desc;

• This is returning recently created posts order
• Now I also want to group my posts by user_name, keeping up the same order.

Example:

user_name	post	created_at
A	X	2021-09-13
B	P	2021-09-12
A	Y	2021-09-12
B	Q	2021-09-11
A	Z	2021-09-11

Requirement

• Group posts by user_name, order by created_at
• Also Note that, created_at sort order should get the first priority, and then group user with order by created_at desc.

user_name	post	created_at
A	X	2021-09-13
A	Y	2021-09-12
A	Z	2021-09-11
B	P	2021-09-12
B	Q	2021-09-11

Answer 1

If you just want to order by username, you would use:

order by user_name, created_at desc

If you want the most recent user first, you can use window functions:

order by max(created_at) over (partition by user_name) desc,
         user_name,
         created_at desc