I have a complex query that I need to do, which could probably be done easily with sub-selects, but mysql doesn't have them.
I could use a temporary table, but I am dealing with a really active table, and I have a feeling that using a temp table is really going to slow things down....
Here's what I have....
A table full of messages. To simplify things, lets say each message has:
Title
PosterName
MessageId
TopicMessageId
PostDate
I need to create a Topic List.
Currently, I have info from the Topic Message
- Title of message
- PosterName
- Topic Message Id
And I have the date of the most recent message. All this is grouped by Topic Message, and then ordered by the most recent message -- giving a list of topics, ordered by whichever most recent activity.
Code I am using looks like this:
select Title,PosterName,TopicMessageId, max(PostDate)MostRecentMessage from Message group by TopicMessageId order by MostRecentMessage desc
Here is the problem -- my client wants the PosterName of the most recently posted topic in the topic list as well. I think, with a sub-select, I could it, but without, I have no idea.
If anyone has the time to give some advice here, I would be extremely grateful.. I've been losing sleep for a few days over this, and if there is a way to do it, I haven't found it.
I could use a temporary table, but I am dealing with a really active table, and I have a feeling that using a temp table is really going to slow things down....
Here's what I have....
A table full of messages. To simplify things, lets say each message has:
Title
PosterName
MessageId
TopicMessageId
PostDate
I need to create a Topic List.
Currently, I have info from the Topic Message
- Title of message
- PosterName
- Topic Message Id
And I have the date of the most recent message. All this is grouped by Topic Message, and then ordered by the most recent message -- giving a list of topics, ordered by whichever most recent activity.
Code I am using looks like this:
select Title,PosterName,TopicMessageId, max(PostDate)MostRecentMessage from Message group by TopicMessageId order by MostRecentMessage desc
Here is the problem -- my client wants the PosterName of the most recently posted topic in the topic list as well. I think, with a sub-select, I could it, but without, I have no idea.
If anyone has the time to give some advice here, I would be extremely grateful.. I've been losing sleep for a few days over this, and if there is a way to do it, I haven't found it.