Try this:
select root_msg_id, msg_id, posting_time
from bboard
where msg_id in
(select max(msg_id) from bboard group by root_msg_id)
order by posting_time;
My bboard table looks like this...
select root_msg_id, msg_id, posting_time
from bboard
order by root_msg_id;
root_msg_id | msg_id | posting_time
-------------+--------+------------------------
000132 | 000132 | 2000-10-31 13:35:04+00
000133 | 000133 | 2000-10-31 18:24:20+00
000135 | 000135 | 2000-11-03 11:51:43+00
000136 | 000136 | 2000-11-03 11:54:27+00
00013D | 00013N | 2000-11-16 12:22:29+00
00013D | 00013D | 2000-11-08 13:23:01+00
00013F | 00013M | 2000-11-09 11:35:11+00
00013F | 00013L | 2000-11-08 14:06:44+00
00013F | 00013I | 2000-11-08 13:52:41+00
00013F | 00013F | 2000-11-08 13:27:52+00
00013G | 00013J | 2000-11-08 13:57:05+00
00013G | 00013G | 2000-11-08 13:33:03+00
00013H | 00013K | 2000-11-08 14:03:06+00
00013H | 00013H | 2000-11-08 13:35:32+00
(14 rows)
And that command gives me the following results...
root_msg_id | msg_id | posting_time
-------------+--------+-----------------------
000132 | 000132 | 2000-10-31 13:35:04+00
000133 | 000133 | 2000-10-31 18:24:20+00
000135 | 000135 | 2000-11-03 11:51:43+00
000136 | 000136 | 2000-11-03 11:54:27+00
00013G | 00013J | 2000-11-08 13:57:05+00
00013H | 00013K | 2000-11-08 14:03:06+00
00013F | 00013M | 2000-11-09 11:35:11+00
00013D | 00013N | 2000-11-16 12:22:29+00
(8 rows)
There may be better solutions...I'm used to using Sybase which has some non-standard behavior in terms of groups by's and having's.
Cheers,
