Revise query to sort by date

[calista]

Here is the current query from my discussion board.

<CFQUERY NAME=&quot;GetThreads&quot; DATASOURCE=&quot;#Application.Datasource#&quot;>
	SELECT 	ThreadID,
			ThreadName, 
			ThreadDate,
			ThreadForumID,
			Count(MessageID) AS MessCount,
			Max(MsgDatePosted) as lastpost
	FROM 	ThreadTable, 
			MessageTable
	WHERE 	ThreadID = MsgThreadID
	AND		ThreadForumID = '#Forum#'
	GROUP BY 	ThreadForumID,
				ThreadID, 
				ThreadName,
				ThreadDate
	ORDER BY	ThreadName
</CFQUERY>

This works fine, but now I have a request to allow the user to order the list by ThreadName or LastPost. I've tried ORDER BY Max,LastPost, and MsgDatePosted. In the first two cases, I get an error that CF can't determine the value of the parameter, and in the last case, it diplays all messages, not just the date of the most recent post. This can't be that hard, but I don't know what to try next. Calista :-X
Jedi Knight,
Champion of the Force

 

[Glowball]

If you do

ORDER BY MsgDatePosted DESC

will it not give you what you want? That should order by the newest first. If you just want posts from today and you are using Oracle then you need

WHERE MsgDatePosted = SYSDATE

You should replace

SYSDATE

with whatever your database uses to grab the current timestamp. You could also use

#Now()#

though the database-only method should be more efficient.

Does that help?

 

[calista]

Unfortunatly, no. I'll try to better explain what I am looking for. Currently, I display the results of that query in a table that lists the thread name, the total number of messages in that thread, and the date/time of the most recent message in that thread, and the list is ordered by ThreadName. What I would like to do is display the same information ordered by LastPost, so the threads with the most recent activity are shown first. If I ORDER BY MsgDatePosted, I get ThreadName,number of messages, and MsgDatePosted for each message in the thread.

Thanks for your help! Calista :-X
Jedi Knight,
Champion of the Force

 

[Glowball]

Sorry about the delay! Though subqueries could be an option those would run pretty slowly. What if you do your query that is working and put the results in an array, one for each record? Then you could order the array. Do something like so:

<cfset aRecords = ArrayNew(1)>
<cfloop query=&quot;GetThreads&quot;>
    <cfset key = DateFormat(GetThreads.lastpost,'yyyymmdd') & TimeFormat(GetThreads.lastpost,'hhmm')>
    <cfset aRecords[#key#] = &quot;<tr><td>&quot; & GetThreads.ThreadName & &quot;</td><td>&quot; & GetThreads.MessCount & &quot;</td></tr>&quot;>
</cfloop>
<cfset aRecordsSort = ArrayNew(1)>
<cfset aRecordsSort = ArraySort(aRecords, &quot;numeric&quot;, &quot;desc&quot;)>
<cfoutput>
<table>
<cfloop from=&quot;1&quot; to=&quot;#ArrayLen(aRecordsSort)#&quot; index=&quot;i&quot;>
    #aRecordsSort[i]#
</cfloop>
</table>
</cfoutput>

It's a little weird but it would work.

 

[calista]

Thanks! I've actually used that technique in another place. I put the results of the query in a two dimensional array, and sort it two diferent ways depending on user choice.

I was assuming there was a way to order by LastPost, since I refer to that variable later in the processing and it is available there, but it must not get set until the query is finished. The end result I am trying to achieve is to give the user the option of view the list of threads in the discussion forum in alphabetical order, by LastPost, or maybe even the number of messages in the thread, by clicking on the title.

Thanks for your help! Calista :-X
Jedi Knight,
Champion of the Force