We help IT Professionals succeed at work.

Custom Order by

al4629740 asked
Last Modified: 2019-01-24
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

Watch Question

lcohanDatabase Analyst

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


Is there anyway to do this in one query
Shaun KlineLead Software Engineer

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.
Database Developer
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
Doug BishopDatabase Developer

@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 Advisor
Most Valuable Expert 2014
Awarded 2013

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.

   CASE WHEN H.Agency = 'Administrator' THEN 2 ELSE 1 END, H.Agency


Bullseye Doug

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions