PostgreSql: order result - II

REFER: postgresql: ordered result

I asked this question and accepted the answer. Well, at the time of asking I had something different in my mind, but I was convinced by the accepted answer. Well, I recently realized that the accepted answer is not something I wanted. Well, I'm restating the question:

I've a table which looks like:

 id | user_id | activity_id | activity_type | root_id | is_root | timestamp 
----+---------+-------------+---------------+---------+---------+-----------
  1 |       1 |           1 | text          |       1 |       1 |       200
  2 |       2 |           2 | text          |       1 |       0 |       206
  3 |       3 |           3 | text          |       1 |       0 |       210
  4 |       2 |          10 | text          |      10 |       1 |        50
  5 |       1 |          11 | text          |      10 |       0 |        90
  6 |       3 |          12 | text          |      10 |       0 |       100
  7 |       3 |          20 | text          |      20 |       1 |       120
  8 |       2 |          21 | text          |      20 |       0 |       130
  9 |       3 |          22 | text          |      20 |       0 |       150
 10 |       3 |          22 | text          |      20 |       0 |       150
 11 |       3 |          22 | text          |      20 |       0 |       190

The output that I'm looking for is:

 id | user_id | activity_id | activity_type | root_id | is_root | timestamp 
----+---------+-------------+开发者_StackOverflow社区---------------+---------+---------+-----------
  1 |       1 |           1 | text          |       1 |       1 |       200
  2 |       2 |           2 | text          |       1 |       0 |       206
  3 |       3 |           3 | text          |       1 |       0 |       210
  7 |       3 |          20 | text          |      20 |       1 |       120
  8 |       2 |          21 | text          |      20 |       0 |       130
 11 |       3 |          22 | text          |      20 |       0 |       150
  9 |       3 |          22 | text          |      20 |       0 |       150
 10 |       3 |          22 | text          |      20 |       0 |       190
  4 |       2 |          10 | text          |      10 |       1 |        50
  5 |       1 |          11 | text          |      10 |       0 |        90
  6 |       3 |          12 | text          |      10 |       0 |       100

• The root_id should be placed in one group, and the first row of that group should have is_root = 1.
• The groups should be sorted on the basis of timestamp of root DESC, but the children of the root should be sorted ASC (timestamp based)

The relevant columns for the question is root_id, is_root, timestamp.

Any help is appreciated.

Thanks

select
    id,
    user_id, 
    activity_id,
    activity_type,
    t.root_id,
    is_root,
    timestamp
from t
inner join (
    select root_id, max(timestamp) as root_id_max_timestamp
    from t
    group by root_id
) root_id_timestamp on t.root_id = root_id_timestamp.root_id
order by
    root_id_max_timestamp desc,
    is_root = 1 desc,
    timestamp
;

Your output sample data is not the same as the input. As example the timestamp of the input id #7 is 190 while the one of the output is 120. So check your output before you consider this query wrong.