postgresql: ordered result

Question

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 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

Jonathan Hall · Accepted Answer · 2011-07-08 19:59:41Z

2

Are you talking about this?

ORDER BY root_id DESC, is_root DESC, timestamp

answered Jul 8, 2011 at 19:59

Jonathan Hall

80.5k19 gold badges162 silver badges206 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mayank Over a year ago

Ohh... right... it's simple :). I don't why it did not come in my head :(. Anyways, thanks a lot for the help

JB Nizet · Accepted Answer · 2011-07-08 20:00:36Z

2

order by root_id desc, is_root desc, timestamp asc

should do the trick.

answered Jul 8, 2011 at 20:00

JB Nizet

694k94 gold badges1.3k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

postgresql: ordered result

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related