Custom Order by

In the following query, I order the output based upon Agency at the end.  The problem though is that one of the agencies in the list needs to always be listed at the bottom after all the other agencies are listed in alphabetical order.  Is there any way to accomplish that in SQL?

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) 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

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.

lcohanDatabase AnalystCommented:
I would use 2CTE's - first excluding the one
always be listed at the bottom after all the other agencies are listed in alphabetical order
and second that has only that single one then use a SQL UNION to select from both CTE's
al4629740Author Commented:
Is there anyway to do this in one query
Shaun KlineLead Software EngineerCommented:
Another possible option is to use a CASE statement in your ORDER BY:

CASE when H.Agency= "X" THEN 2 ELSE 1 END, H.Agency

Not sure how this would affect your query's performance, though.
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Doug BishopDatabase DeveloperCommented:
ORDER BY CASE H.Agency WHEN 'my special agency' THEN 2 ELSE 1 END, H.Agency

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
Doug BishopDatabase DeveloperCommented:
@lcohan, the problem with that is you still have to select data from the UNIONed sources and SQL does not guarantee an order of retrieval UNLESS you use an ORDER BY clause. Even on an IDENTITY column, if you just say SELECT column FROM table, although the data might be returned in the correct order, there is no way to insure it does every time unless the data is ordered.
PortletPaulEE Topic AdvisorCommented:
If you use "select distinct" in the cte, then do counts on that cte, you run the risk of incorrect results. I suggest you don't need the cte, or at least that you remove distinct.

Also, your date range has 2 possible problem:
a. less than December 31 means you are excluding that day from the data - not sure if that is what you expected? A more typical date range would be less than January 1st  so that all of December would be considered. in short I think your current query is 24 hours short of 6 months.
b. date literals in dd/mm/yyyy are not "safe", depending on what database it is and the database settings. If this is for SQL Server then the safest date literal style is yyyymmdd like so:

    AND H.Fiscal = 2019
    AND H.ActivityDate >= '20180701'
    AND H.ActivityDate < '20190101'
    AND R.RegDate >= '20180701'
    AND R.RegDate < '20190101'


Then, as has already been suggested, use a CASE EXPRESSION in the order by clause - this is the most appropriate method; E.G.

ORDER BY
   CASE WHEN H.Agency = 'Administrator' THEN 2 ELSE 1 END, H.Agency
al4629740Author Commented:
Bullseye Doug
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.