On 10/26/07, brian <brian@xxxxxxxxxxxxxxxx> wrote: > Pat Maddox wrote: > > Right now my table looks like this: > > > > posts > > id > > body > > parent_id > > root_id > > created_at > > > > so if I've got the records > > > > (1, 'post 1', NULL, 1, '4pm') > > (2, 'post 2', NULL, 2, '8pm') > > (3, 'post 3', 1, 1, '6pm') > > (4, 'post 4', 1, 1, '5pm') > > (5, 'post 5', 4, 1, '6pm') > > (6, 'post 6', NULL, 1, '5pm') > > > > I'd like to do a select and get them all in this order: > > > > (1, 'post 1', NULL, 1, '4pm') > > (4, 'post 4', 1, 1, '5pm') > > (5, 'post 5', 4, 1, '6pm') > > (3, 'post 3', 1, 1, '6pm') > > (6, 'post 6', NULL, 1, '5pm') > > (2, 'post 2', NULL, 2, '8pm') > > > > And reverse sorted would be: > > > > (2, 'post 2', NULL, 2, '8pm') > > (6, 'post 6', NULL, 1, '5pm') > > (1, 'post 1', NULL, 1, '4pm') > > (3, 'post 3', 1, 1, '6pm') > > (4, 'post 4', 1, 1, '5pm') > > (5, 'post 5', 4, 1, '6pm') > > > > > SELECT * FROM posts ORDER BY root_id, id; > > brian > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org/ > Okay, but what if I want to order by created_at? btw created_at is a timestamp, I just wrote '4pm' to make it a bit easier to read. Pat ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
