MySQL sort of max select

[WebmasterA]

Hello,

I have a sort question.
I have two table: topics and messages for those topics.
Each topic can have multiple messages.
I need to select the topic name and the last message posted.

topics table
topic_id (int)
topic_header (varchar 100)
topic_text (text)

messages table
mesage_id (int)
topic_id (int)
message_text (text)
message_date (datetime)

Here is my select statement

select a.topic_id, a.topic_header, max(messsages.message_date)
from topics a
left outer join messages(a.topic_id=messages.topic_id)
order by messages.message_date

Unfortunately order by in this case sorts ALL the messages. I need to sort only the selected MAX ones.

Does anyone have an idea how to do it.

Thank you.

- Andrey

P.S. please let me know if you have any questions about my problem

 

[azzazzello]

Try this, I think...

select a.topic_id, a.topic_header, max(messsages.message_date) as maxdate
from topics a
left outer join messages(a.topic_id=messages.topic_id)
order by maxdate

 

[WebmasterA]

Thanks for reply.

I am not sure what maxdate suppose to be.
If you are proposing to try:

select a.topic_id, a.topic_header, max(messsages.message_date)
from topics a
left outer join messages(a.topic_id=messages.topic_id)
group by a.topic_id <==== missed line
order by max(messages.message_date)

unfortunately that does not work.

I forgot one line before.

- Andrey

 

[azzazzello]

doh! sorry, I myself confused group by and order by

 

[sylve]

If you want to make your life easier, put a alias for group fields (just like you did for your topics table). Azza's maxdate is just that:

select a.topic_id, a.topic_header, 
max(messsages.message_date) [red]as maxdate[/red]
from topics a
left outer join messages(a.topic_id=messages.topic_id)
group by a.topic_id
order by maxdate

 

[WebmasterA]

It works like a charm.
Thanks a lot guys.

Sorry azzazzello I misread your first message a little bit.

Thanks again guys.

- Andrey