Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.
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
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
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.
LVL 42
I would use 2CTE's - first excluding the one
and second that has only that single one then use a SQL UNION to select from both CTE's
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.
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
@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
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.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.