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

SQL Group BY - need help with syntax/usability & ColdfFusion

I have a query:

<cfquery name="qry_getEdQueue" datasource="#request.DSN#">
SELECT 

    	DISTINCT tblActivities.activityID
        , tblActivities.activityApprovedDate 
        , tblActivityAuthors.personID 
        , tblActivityAuthors.resumeDisplay
        , tblPerson.personType 
  		, tblPerson.personLast 
        , tblPerson_RTIDetail.personUnit  
        , tblCitations.citationFull 
        , lutblActivityResumeStatus.activityResumeStatus 
        
    FROM tblActivities 
    
    		JOIN tblActivityAuthors ON tblActivities.activityID = tblActivityAuthors.activityID    
    		JOIN tblPerson ON tblActivityAuthors.personID = tblPerson.personID 
            JOIN tblPerson_RTIDetail ON tblPerson_RTIDetail.personRTIID = tblPerson.personRTIID 
            JOIN tblCitations ON tblActivityAuthors.activityID = tblCitations.activityID 
            LEFT JOIN lutblActivityResumeStatus ON lutblActivityResumeStatus.activityResumeStatusID = tblActivityAuthors.activityResumeStatus 
    
    	WHERE activityProcessingStatus = '12' 
        AND tblActivityAuthors.resumeDisplay = '1'  
        <!--- do not include non-RTI Authors or Groups which are lutblPersonTypes LT 4 --->
        AND tblPerson.personType < 4 
        <!--- place units to look for with Editor Admin here --->
        
        ORDER BY tblPerson_RTIDetail.personUnit, tblPerson.personLast 
        
</cfquery>

Open in new window


which produces these results:

 qry dump
I don't know if it's possible, but I'd like to group the results by personLast so I could produce rows of data like this:

 front end
that only list the personUnit once, then the personID multiple times with their information.

I hope this makes sense. I doubt it does! Feel free to ask!
0
traport
Asked:
traport
  • 2
1 Solution
 
cfEngineersCommented:
Try this
<cfquery name="qry_getEdQueue" datasource="#request.DSN#">
SELECT 

    	DISTINCT tblActivities.activityID
        , tblActivities.activityApprovedDate 
        , tblActivityAuthors.personID 
        , tblActivityAuthors.resumeDisplay
        , tblPerson.personType 
  		, tblPerson.personLast 
        , tblPerson_RTIDetail.personUnit  
        , tblCitations.citationFull 
        , lutblActivityResumeStatus.activityResumeStatus 
        
    FROM tblActivities 
    
    		JOIN tblActivityAuthors ON tblActivities.activityID = tblActivityAuthors.activityID    
    		JOIN tblPerson ON tblActivityAuthors.personID = tblPerson.personID 
            JOIN tblPerson_RTIDetail ON tblPerson_RTIDetail.personRTIID = tblPerson.personRTIID 
            JOIN tblCitations ON tblActivityAuthors.activityID = tblCitations.activityID 
            LEFT JOIN lutblActivityResumeStatus ON lutblActivityResumeStatus.activityResumeStatusID = tblActivityAuthors.activityResumeStatus 
    
    	WHERE activityProcessingStatus = '12' 
        AND tblActivityAuthors.resumeDisplay = '1'  
        <!--- do not include non-RTI Authors or Groups which are lutblPersonTypes LT 4 --->
        AND tblPerson.personType < 4 
        <!--- place units to look for with Editor Admin here --->
        
        ORDER BY tblPerson_RTIDetail.personUnit, tblPerson.personLast 
        
</cfquery>

<cfoutput query="qry_getEdQueue" group="personLast">
	<b>#personLast#</b><br>
	<cfoutput>
 		<li>#activityid#</li>
 	</cfoutput>
</cfoutput>

Open in new window

0
 
traportAuthor Commented:
Genius! Many thanks.
0
 
cfEngineersCommented:
Any Time
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

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