postgresql: ordered result

I've a table that looks like:

 id | user_id | activity_id | activity_type | root_id | is_root | timestamp 
----+---------+-------------+---------------+---------+---------+-----------
  1 |       1 |           1 | text          |       1 |       1 |         5
  2 |       2 |           2 | text          |       1 |       0 |         6
  3 |       3 |           3 | text          |       1 |       0 |        10
  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 |       190
  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 |       150

I want an out put like

 id | user_id | activity_id | activity_type | root_id | is_root | timestamp 
----+---------+-------------+---------------+---------+---------+-----------
  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 |       150
  4 |       2 |          10 | text          |      10 |       1 |        50
  5 |       1 |          11 | text          |      10 |       0 |        90
  6 |       3 |          12 | text          |      10 |       0 |       100
  1 |       1 |           1 | text          |       1 |       1 |         5
  2 |       2 |           2 | text          |       1 |       0 |         6
  3 |       3 |           3 | text          |       1 |       0 |        10

The root_id s开发者_如何学Pythonhould 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

Are you talking about this?

ORDER BY root_id DESC, is_root DESC, timestamp

order by root_id desc, is_root desc, timestamp asc

should do the trick.