• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 82
  • Last Modified:

another question with Coldfusion queries and group by

I had this question after viewing Getting data within a CFLOOP.

Hell again!! I wanted to try to expand on a functionality that I was helped with on here, and hopefully it will be a simple oversight on my part, but I wanted to get all of the records for one project and have those records placed in a textarea box, and that works great. But I also want to have a second textarea box that only shows the most recent comment per project number, and that textarea box will be modifiable, so a user can change the change inside that single comment on that textarea box. The code I have is below, but I can't seem to get the query right to get the single record for one textarea, while also getting all records for the other textarea.

<CFQUERY DATASOURCE="PDBARMYDEV" NAME="CecomInput">
 SELECT current_bulk_filter.proj_no,
current_bulk_filter.capces_location,
current_bulk_filter.capces_state,
current_bulk_filter.capces_description,
capces_support_history.proj_closed_reason

 FROM   mca.current_bulk_filter
   
left join mca.capces_support_history
on current_bulk_filter.proj_no = capces_support_history.proj_no  

where capces_support_history.proj_closed_reason is not null      
 order by current_bulk_filter.proj_no asc
</CFQUERY>



<cfoutput query="CecomInput" group="proj_no">
       <!--- This will print once for every project --->
       proj_no = #proj_no#<br>
       <!--- This will print each reason within a single textarea --->
     <textarea><cfoutput>#proj_closed_reason##chr(10)#</cfoutput></textarea>
     <br>
</cfoutput>
0
diecasthft01
Asked:
diecasthft01
  • 2
1 Solution
 
_agx_Commented:
Just change the ORDER BY to also sort by a column in the history table so you get the newest reasons first.  I don't know the columns in your table, but say you had a "DateUpdated" column, sort by:

          ORDER BY current_bulk_filter.proj_no, capces_support_history.DateUpdated DESC

Then just add another textarea to the  outer cfoutput. That'll display the newest reason automatically.

<cfoutput query="CecomInput" group="proj_no">
       <!--- These values will only print once for every project --->
       proj_no = #proj_no#<br>
       <!--- Display first reason --->
      <textarea>#proj_closed_reason#</textarea>

       <!--- This will print each reason within a single textarea --->
     <textarea><cfoutput>#proj_closed_reason##chr(10)#</cfoutput></textarea>
     <br>
</cfoutput> 

Open in new window

0
 
diecasthft01Author Commented:
Perfect!!! Thank you so much!!!
0
 
_agx_Commented:
Glad it helped!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now