showing distinct information for individual records in a grouped output

Got another one that is giving us a headache over here:

I have a number of queries which utilize "group by." This works great except when grabbing data from multiple queries.

For example, I have a query "details" that grabs a bunch of related data across 5 tables and has an "order by."

Then to get the most recent entry date:

<CFQUERY NAME= "lastDate" DATASOURCE= "QMB_DB">select max(DATE_CREATED) date_created from qmb where qmb.pid = '#details.pid#' or qmb.qid = '#details.pid#'</CFQUERY>


In the output:

<CFOUTPUT QUERY= "details" group="NAME">
<CFOUTPUT>
<tr>
#name#
#lastDate.date_created#
</tr>
</CFOUTPUT>
</CFOUTPUT>

This should give me rows representing indivual query results and the most recent "date created" for the records associated with that particular row.

Unfortunately, what it does is grab the first "created_by" date and applies it to every row.

Any ideas?

You can try it without the second set of <cfoutput> tags. But regardless, won't every record displayed have the same value for lastdate.date_created because that query only returns 1 record (the max date)? You only have one value for lastdate.date_created, so you will naturally have the same value for that variable every time you output it.

On the forum page here it lists various threads. For each thread it not only shows when the last entry was made in that thread, but who made that entry.

How can you do that in ColdFusion with Oracle?

You would return a query with each row containing the thread information (title, thread starter, etc.) for each thread, along with the most recent time each thread was posted to. If you use the max() function, you must remember that if you mix column data and aggregate functions, you must use a GROUP BY clause in your SQL statement.

I am using GROUP BY statements.

Are you saying that each "post" record would need a data field for information on the thread? I don't have that. I thought I could run a query which compared the associated posts and then provide a seperate count for each output row, and display the most recent "date_created" for the associated records represented in each output row.

Do I need to change the DB structure to make this work?

At this point, the "count" displayed in each row is the count calculation for the first row only, and the "last post date" in each row is also based only on the first row calculation.

Yes, each record that you are displaying would have a field for the date created, something like this:

select max(p.DATE_CREATED) lastPostDate, t.threadName
from threads t, posts p
where t.threadID = p.threadID
group by t.threadName

Now if you output each thread name, you also have the date of the most recent post for that thread. You should not need to change your database at all.

No threads table.

Currently there is a post table with a post_id. Associations are established by a question_id field which is populated only in responses with the value of the post_id for the first post of the thread. So the question_id field for the second and all subsequent posts would be equal to the value of the post_id for the first post.

So, one query identifies the questions (question_id field is null).

Another query extracts the datafields for the records identified in the first query.

The count query that should count the number of answers to each question just grabs for the first question only.

The lastdate query that should grab the most recent date_created for answers to a question also only grabs the data for the first record.

There is something we are missign here when we have a grouped output and are using multiple queries.

If/when I find the answer I'll post it here for you. I know it can be done because I've seen it done in CF based message boards. One place even provided a demo. Unfortunately, the code was encrypted so I couldn't see how they structured the queries.

I was just following your example scenario about this forum and the way it displays threads and the date of the last post in each thread.

I whipped up a simple access table which had a post_id, question_id, date_created, and questionText. question_id is null for new questions, and has the post_id of the original post if it is an answer to a post. This query gets all the posts, the count of replies, and the date of the most recent reply. It could probably be cleaned up (or collapsed into one query through the use of a LEFT OUTER JOIN) but I only had a few minutes:

select q1.post_ID, q1.questionText, count(*) as postInThread, max(q2.date_created) as mostRecentPost
from qmb q1,
qmb q2
where q1.question_ID IS NULL
and q1.post_id = q2.question_id
group by q1.post_ID, q1.questionText
order by q1.post_ID
UNION
select q1.post_ID, q1.questionText, 0, date_created
from qmb q1
where q1.question_ID IS NULL and not exists (select 1 from qmb q2 where q1.post_id = q2.question_id)

I sent you a message ... did you get it?

I did, and sent you an email with the database attached, it's a very simple setup but it should demonstrate the use of the query I posted.

It was a bit tricky, but we got it implemented. Here is the complete final query:

select qmb_user.first_name,qmb_user.last_name,qmb_user.email,
qmb_topic_post.topic_id, qmb_post.post_id, qmb_post.post_name,
qmb_post.post_desc, qmb_post.user_id,
TO_CHAR(qmb_post.date_created,'MM/DD/YYYY HH24:MI') date_created,
(select TO_CHAR( max( DATE_CREATED ) ,'MM/DD/YYYY HH24:MI' ) from QMB_POST a where a.QUESTION_id= qmb_post.post_id group by qmb_post.post_id) lastdate,
QMB_TOPIC.topic_name, QMB_TOPIC.topic_id,
(select count(*) from qmb_post b
where b.question_id is not null
and b.question_id=qmb_post.post_id group by b.question_id) anscount
from qmb_post,
qmb_forum, qmb_topic_post, qmb_topic, qmb_user
where qmb_post.post_id = qmb_topic_post.post_id
and qmb_topic_post.topic_id = qmb_topic.topic_id
and qmb_topic.forum_id = qmb_forum.forum_id
and qmb_forum.forum_id = '#FORUM_ID#'
and qmb_post.question_id IS NULL
and qmb_user.user_id = qmb_post.user_id
order by qmb_topic_post.topic_id,qmb_post.post_id