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
Database Analyst
CERTIFIED EXPERT
I would use 2CTE's - first excluding the one
always be listed at the bottom after all the other agencies are listed in alphabetical orderand second that has only that single one then use a SQL UNION to select from both CTE's
Lead Software Engineer
CERTIFIED EXPERT
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.
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
@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.
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
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
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.
Get AccessWhy 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
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