sort sql by date from 2 different tables
Hi
i have 2 tables.
1 table is a my MAIN MESSAGE POST
the other table is the COMMENTS posted to messages.
my recordset pulls the main message, and then pulls the JOINED comments matching that
what i need to do is the get date field from both tables and have the data sorted by the most recent date.
everything i have tried i either get things sorted by the most recent message where messages with comments do not get identified as newly updated - they are just sorted by the date of the original message.
OR I get the most recent comment posting date, where the new messages posted with no comments go to the end.
What i need is to get the records sorted so that they sort by the most recent.
Recent meaning most recent message posted, or recent comment.
heres the code i have currently for this recordset.
any help would be much appreciated.
<%
Dim rsMainPage
Dim rsMainPage_cmd
Dim rsMainPage_numRows
Set rsMainPage_cmd = Server.CreateObject ("ADODB.Command")
rsMainPage_cmd.ActiveConnection = MM_connBoard_STRING
rsMainPage_cmd.CommandText = "SELECT blg_topic_top.id_top, blg_topic_top.title_top, blg_article_art.id_art, blg_article_art.title_art, blg_article_art.description_art, blg_article_art.date_art , blg_article_art.visible_art, count(blg_comment_com.id_com) AS count_id_com_1, max(blg_comment_com.date_com), blg_article_art.text_art FROM ((blg_topic_top LEFT JOIN blg_article_art ON blg_article_art.idtop_art=blg_topic_top.id_top) LEFT JOIN blg_comment_com ON blg_comment_com.idart_com=blg_article_art.id_art) WHERE blg_article_art.visible_art=1 GROUP BY blg_topic_top.id_top, blg_topic_top.title_top, blg_article_art.id_art, blg_article_art.title_art, blg_article_art.description_art, blg_article_art.date_art, blg_article_art.visible_art, blg_article_art.text_art ORDER BY max(blg_comment_com.date_com) DESC"
rsMainPage_cmd.Prepared = true
Set rsMainPage = rsMainPage_cmd.Execute
rsMainPage_numRows = 0
%>
THANKS
sam