Custom Order by

al4629740
al4629740 used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

Commented:
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

Author

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

Commented:
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.
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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

Commented:
@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

Commented:
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

Author

Commented:
Bullseye Doug

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