Query Syntax on Order By in Inner Join statement

al4629740
al4629740 used Ask the Experts™
on
I am having trouble figuring out how to order the following SQL query.  When I add "order by H.Agency, H.ActivityType" at the end of the query I get a syntax error near keyword order.  Please help!!

With CTE_Hours as (select distinct H.AgencyID, H.Agency,H.Hours, isnull(H.Classification,'') Classification, isnull(H.Objectives,'') Objectives,H.ActivityType,H.ActivityOther, H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther, H.ActivityID, H.RegID , R.AgeCurrent, R.CommunityCommittee, R.YouthCommittee, R.Parentcheck, R.CommunityResident, R.Race, R.Gender, R.Sector , cast(H.ActivityDate as Date) ActivityDate, H.Fiscal from tblOrgHours H inner join tblOrgRegistrations R on H.Regid = R.RegID and R.AgeCurrent between 0 and 99 Where 
 (H.Agency = 'Administrator' OR H.Agency = 'Chicago Area Project -')  And H.Fiscal = 2019 And H.ActivityDate >= '07/01/2018' And H.ActivityDate < '12/31/2018' And R.RegDate >= '07/01/2018' And R.RegDate < '12/31/2018') select  H.Agency ,A.ActivityName ,H.Classification ,H.ActivityDate ,H.Objectives ,H.Hours ,count(A.ActivityName) over (partition by H.Agency,A.ActivityName order by A.activityName) as [ActivityCount] ,Count(H.RegID) as [# individuals] ,H.[ActivityType],H.ActivityOther,H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther ,COUNT(CASE when R.CommunityCommittee = '1' then 1 end) as [CommunityCommittee]
,COUNT(CASE when R.YouthCommittee = '1' then 1 end) as [YouthCommittee] ,COUNT(CASE when R.Parentcheck = '1' then 1 end) as [Parentcheck] ,COUNT(CASE when R.CommunityResident = '1' then 1 end) as [CommunityResident] ,COUNT(CASE when R.Sector = 'Business' then 1 end) as [Business] ,COUNT(CASE when R.Sector = 'Civic-Volunteer' then 1 end) as [Civic-Volunteer] ,COUNT(CASE when R.Sector = 'Community Resident' then 1 end) as [Community Resident] ,COUNT(CASE when R.Sector = 'Faith Based' then 1 end) as [Faith Based] ,COUNT(CASE when R.Sector = 'Healthcare' then 1 end) as [Healthcare]
,COUNT(CASE when R.Sector = 'Human Support Agencies' then 1 end) as [Human Support Agencies] ,COUNT(CASE when R.Sector = 'Law Enforcement' then 1 end) as [Law Enforcement] ,COUNT(CASE when R.Sector = 'Local Government' then 1 end) as [Local Government] ,COUNT(CASE when R.Sector = 'Media' then 1 end) as [Media] ,COUNT(CASE when R.Sector = 'Parent or Guardian' then 1 end) as [Parent or Guardian] ,COUNT(CASE when R.Sector = 'Philanthropic' then 1 end) as [Philanthropic] ,COUNT(CASE when R.Sector = 'Schools' then 1 end) as [Schools] ,COUNT(CASE when R.Sector = 'Youth' then 1 end) as [Youth]
 from CTE_Hours H inner join tblOrgRegistrations R on H.Regid = R.RegID inner join tblOrgActivities A on H.ActivityID = A.ActivityID group by H.Agency,H.Classification,H.Hours, A.ActivityName,H.ActivityDate, H.Objectives,H.ActivityType,H.ActivityOther,H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther Order by H.Agency, H.Classification,A.ActivityName, H.ActivityDate order by H.ActivityType
 

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
You canot have two order by clauses on the same query.

At the end of that you have:
Order by H.Agency, H.Classification,A.ActivityName, H.ActivityDate order by H.ActivityType
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
You might want to format the SQL better to see things like this.

For example, here is how I noticed it.  I formatted the SQL and indented.  Once I got the to last order by, I noticed the extra one.
With CTE_Hours as (
	select distinct H.AgencyID, H.Agency,H.Hours, isnull(H.Classification,'') Classification, isnull(H.Objectives,'') Objectives,
		H.ActivityType,H.ActivityOther, H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther, H.ActivityID, H.RegID , 
		R.AgeCurrent, R.CommunityCommittee, R.YouthCommittee, R.Parentcheck, R.CommunityResident, R.Race, R.Gender, R.Sector , 
		ast(H.ActivityDate as Date) ActivityDate, H.Fiscal
	from tblOrgHours H 
	inner join tblOrgRegistrations R on H.Regid = R.RegID and R.AgeCurrent between 0 and 99
	Where (H.Agency = 'Administrator' OR H.Agency = 'Chicago Area Project -')  And H.Fiscal = 2019 And
		H.ActivityDate >= '07/01/2018' And H.ActivityDate < '12/31/2018' And R.RegDate >= '07/01/2018' And R.RegDate < '12/31/2018'
)
select  H.Agency ,A.ActivityName ,H.Classification ,H.ActivityDate ,H.Objectives ,H.Hours ,
		count(A.ActivityName) over (partition by H.Agency,A.ActivityName order by A.activityName) as [ActivityCount] ,
		Count(H.RegID) as [# individuals] ,H.[ActivityType],H.ActivityOther,H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther ,
		COUNT(CASE when R.CommunityCommittee = '1' then 1 end) as [CommunityCommittee],
		COUNT(CASE when R.YouthCommittee = '1' then 1 end) as [YouthCommittee] ,
		COUNT(CASE when R.Parentcheck = '1' then 1 end) as [Parentcheck] ,
		COUNT(CASE when R.CommunityResident = '1' then 1 end) as [CommunityResident] ,
		COUNT(CASE when R.Sector = 'Business' then 1 end) as [Business] ,
		COUNT(CASE when R.Sector = 'Civic-Volunteer' then 1 end) as [Civic-Volunteer] ,
		COUNT(CASE when R.Sector = 'Community Resident' then 1 end) as [Community Resident] ,
		COUNT(CASE when R.Sector = 'Faith Based' then 1 end) as [Faith Based] ,
		COUNT(CASE when R.Sector = 'Healthcare' then 1 end) as [Healthcare],
		COUNT(CASE when R.Sector = 'Human Support Agencies' then 1 end) as [Human Support Agencies] ,
		COUNT(CASE when R.Sector = 'Law Enforcement' then 1 end) as [Law Enforcement] ,
		COUNT(CASE when R.Sector = 'Local Government' then 1 end) as [Local Government] ,
		COUNT(CASE when R.Sector = 'Media' then 1 end) as [Media] ,
		COUNT(CASE when R.Sector = 'Parent or Guardian' then 1 end) as [Parent or Guardian] ,
		COUNT(CASE when R.Sector = 'Philanthropic' then 1 end) as [Philanthropic] ,
		COUNT(CASE when R.Sector = 'Schools' then 1 end) as [Schools] ,
		COUNT(CASE when R.Sector = 'Youth' then 1 end) as [Youth]
 from CTE_Hours H
		inner join tblOrgRegistrations R on H.Regid = R.RegID
		inner join tblOrgActivities A on H.ActivityID = A.ActivityID
group by H.Agency,H.Classification,H.Hours, A.ActivityName,H.ActivityDate, H.Objectives,H.ActivityType,H.ActivityOther,H.Narrative,H.Outcome,H.Duration,
		H.Frequency,H.Strategy,H.Need,H.NeedOther
Order by H.Agency, H.Classification,A.ActivityName, H.ActivityDate
order by H.ActivityType
 

Open in new window

lcohanDatabase Analyst

Commented:
With CTE_Hours as (select distinct H.AgencyID, H.Agency,H.Hours, isnull(H.Classification,'') Classification, isnull(H.Objectives,'') Objectives,H.ActivityType,H.ActivityOther, H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther, H.ActivityID, H.RegID , R.AgeCurrent, R.CommunityCommittee, R.YouthCommittee, R.Parentcheck, R.CommunityResident, R.Race, R.Gender, R.Sector , cast(H.ActivityDate as Date) ActivityDate, H.Fiscal from tblOrgHours H inner join tblOrgRegistrations R on H.Regid = R.RegID and R.AgeCurrent between 0 and 99 Where 
 (H.Agency = 'Administrator' OR H.Agency = 'Chicago Area Project -')  And H.Fiscal = 2019 And H.ActivityDate >= '07/01/2018' And H.ActivityDate < '12/31/2018' And R.RegDate >= '07/01/2018' And R.RegDate < '12/31/2018') select  H.Agency ,A.ActivityName ,H.Classification ,H.ActivityDate ,H.Objectives ,H.Hours ,count(A.ActivityName) over (partition by H.Agency,A.ActivityName order by A.activityName) as [ActivityCount] ,Count(H.RegID) as [# individuals] ,H.[ActivityType],H.ActivityOther,H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther ,COUNT(CASE when R.CommunityCommittee = '1' then 1 end) as [CommunityCommittee]
,COUNT(CASE when R.YouthCommittee = '1' then 1 end) as [YouthCommittee] ,COUNT(CASE when R.Parentcheck = '1' then 1 end) as [Parentcheck] ,COUNT(CASE when R.CommunityResident = '1' then 1 end) as [CommunityResident] ,COUNT(CASE when R.Sector = 'Business' then 1 end) as [Business] ,COUNT(CASE when R.Sector = 'Civic-Volunteer' then 1 end) as [Civic-Volunteer] ,COUNT(CASE when R.Sector = 'Community Resident' then 1 end) as [Community Resident] ,COUNT(CASE when R.Sector = 'Faith Based' then 1 end) as [Faith Based] ,COUNT(CASE when R.Sector = 'Healthcare' then 1 end) as [Healthcare]
,COUNT(CASE when R.Sector = 'Human Support Agencies' then 1 end) as [Human Support Agencies] ,COUNT(CASE when R.Sector = 'Law Enforcement' then 1 end) as [Law Enforcement] ,COUNT(CASE when R.Sector = 'Local Government' then 1 end) as [Local Government] ,COUNT(CASE when R.Sector = 'Media' then 1 end) as [Media] ,COUNT(CASE when R.Sector = 'Parent or Guardian' then 1 end) as [Parent or Guardian] ,COUNT(CASE when R.Sector = 'Philanthropic' then 1 end) as [Philanthropic] ,COUNT(CASE when R.Sector = 'Schools' then 1 end) as [Schools] ,COUNT(CASE when R.Sector = 'Youth' then 1 end) as [Youth]
 from CTE_Hours H inner join tblOrgRegistrations R on H.Regid = R.RegID inner join tblOrgActivities A on H.ActivityID = A.ActivityID group by H.Agency,H.Classification,H.Hours, A.ActivityName,H.ActivityDate, H.Objectives,H.ActivityType,H.ActivityOther,H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther 
  Order by H.Agency, H.Classification,A.ActivityName, H.ActivityDate,H.ActivityType
 

Open in new window

Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Author

Commented:
LOL!  I'm seeing things.  Can't believe I missed that.
you have 2 ORDER by clauses
Order by H.Agency, H.Classification,A.ActivityName, H.ActivityDate order by H.ActivityType
I use poorsman sql formatter in  notepad++, other editors have their own autoformat functions, those help a lot in this cases

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial