Query Syntax on Order By in Inner Join statement

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

al4629740Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) 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
slightwv (䄆 Netminder) 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lcohanDatabase AnalystCommented:
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

Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

al4629740Author Commented:
LOL!  I'm seeing things.  Can't believe I missed that.
aranaCommented:
you have 2 ORDER by clauses
Order by H.Agency, H.Classification,A.ActivityName, H.ActivityDate order by H.ActivityType
aranaCommented:
I use poorsman sql formatter in  notepad++, other editors have their own autoformat functions, those help a lot in this cases
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.