Forum Thread Order by Post Date...Am I DOOMED? 1

[alphacooler]

I think I shot myself in the foot by not normalizing my forum schema. Here is what my table for forum posts looks like:

messageID (unique, auto increment)
threadID
groupID (index)
userID
subject
body
date_added
first_post (yes or no)

So for a specific Group/Forum page, I am trying to get a list of all Threads (first posts...first_post='yes') and order by date of last message in that thread (MAX(date_added) as last_post). However, this won't work with a "WHERE first_post='yes'" condition because then MAX(date_added) yields the first post date.

Any ideas?

FULL QUERY:
SELECT messages.threadID, messages.subject, MIN(messages.date_added) as min_date, messages.date_added, MAX(messages.date_added) as last_message, COUNT(messages.messageID) as num_messages
FROM messages
WHERE messages.groupID = $groupID AND messages.first_post='yes'
GROUP BY messages.threadID
ORDER BY last_message DESC

 

[r937]

try this --

SELECT M.threadID
     , M.subject
     , T.min_date
     , T.last_message
     , T.num_messages
  FROM messages as M
INNER
  JOIN ( 
       select threadid
            , MIN(messages.date_added) as min_date
            , MAX(messages.date_added) as last_message
            , COUNT(*) as num_messages
         from messages
       group
           by threadID
       ) as T
    on T.threadID = M.threadID
 WHERE M.groupID = $groupID 
   AND M.first_post='yes' 
ORDER 
    BY T.last_message DESC

r937.com | rudy.ca

 

[alphacooler]

Thanks for the reply,

I forgot to mention that I am on MySQL 3.23, so I can't utilize subqueries. I take it there is no other way to do this?

Also, I could easily upgrade to MySQL 4.1+, but is there *any* chance that doing so would break some of my current queries? (I can't afford that right now due to how busy I am).

thanks.

 

[r937]

the chance that some query which works fine in 3.23 will stop working in 4.1 is not zero, but it's very close to zero

any other way to do it? sure, just store the results of the subquery into a table, and then run the above query joining the messages to that table instead of to the subquery

r937.com | rudy.ca

 

[alphacooler]

r937,

Could you perhaps give me an example of storing that into a table. Would that be a temporary table, or would it forever exist? (Obviously I've never done this before).

Thanks so much r937, I owe you so much.

 

[r937]

take the subquery sql (everything between the parentheses, but not including the parentheses), append CREATE [TEMPORARY] TABLE mytablename at the front of it, and then run that sql



r937.com | rudy.ca

 

[alphacooler]

append CREATE [TEMPORARY] TABLE mytablename select threadid
, MIN(messages.date_added) as min_date
, MAX(messages.date_added) as last_message
, COUNT(*) as num_messages
from messages
group
by threadID

Like that? Then in another query I could just join that table with the other?

Thanks so very much.

 

[alphacooler]

NM, that worked! Oh goodness, thankyou so much r937.

I will now worship you as my MySQL Idol.