On Tue, Feb 22, 2005 at 15:54:47 +0100,
john-paul delaney <jp@justatest.com> wrote:
>
> Given Sean's answer above, I then managed the simple part to get the author
> name from another table, but was stumped when I thought it
> would be better to return the created date of the last message (having the same
> topic_id) rather than the first one. The last message can be found in
> either of two ways:
> (1) the latest 'created' for a topic_id or
> (2) the highest 'topic_seq' number for a topic id.
>
> I've failed miserably in my attempts - any enlightenment greatly appreciated.
The standard way to do this is to use the max function and select the
row that matches this value.
However the nonstandard DISTINCT ON construct is a better way to go if
you don't need to be portable.