Answered by:
Add Column to Query On the Fly?
Question

Hi,
I'm trying to sort/order this query the way I'd like but I don't have the option of adding a physical column to the table called sort_order. That would be the ideal way to do it, but I'm thinking there is a way using a temp table but I can't seem to get it right.
I'm trying to get the results in this order "Elementary, Middle, High".
My query is below:
SELECT
CASE
WHEN jl.grade_level IN ( 'PreK','K','1','2','3','4','K4' ) THEN 'Elementary'
WHEN jl.grade_level IN ( '58','5','6','7','8' ) THEN 'Middle'
WHEN jl.grade_level IN ( '9','10','11','12','912','High') THEN 'High'
END as grade_level,
COUNT(jl.grade_level) AS NumJobsByGrade
FROM JobList jl
WHERE jl.grade_level <> ''
GROUP BY CASE
WHEN jl.grade_level IN ( 'PreK','K','1','2','3','4','K4' ) THEN 'Elementary'
WHEN jl.grade_level IN ( '58','5','6','7','8' ) THEN 'Middle'
WHEN jl.grade_level IN ( '9','10','11','12','912','High') THEN 'High'
END
ORDER BY grade_levelHere are the way the results look.
grade_level  NumJobsByGrade
Elementary , 2
High, 5
Middle, 2
I'm trying to get them in this order "Elementary, Middle, High".
Any help appreciated...
Westside2008
 Edited by Westside2008 Tuesday, March 29, 2011 5:22 PM
Tuesday, March 29, 2011 5:12 PM
Answers

You can in this particular case repeat the exact same expression in the ORDER BY or alternatively give a different name to the resulting column (Say, GradeLevel) and use this alias or use ordinal position.
BTW, for your amusement and to be aware of possible problems I suggest this blog post
The Troll's Puzzle  a SQL fable
Update, I see you want to order them not in the character order. You need to use
SELECT f.grade_level, Count(jl.grade_level) AS numjobsbygrade FROM joblist jl CROSS APPLY (SELECT CASE WHEN jl.grade_level IN ('PreK','K','1','2', '3','4','K4') THEN 'Elementary' WHEN jl.grade_level IN ('58','5','6','7', '8') THEN 'Middle' WHEN jl.grade_level IN ('9','10','11','12', '912','High') THEN 'High' END AS grade_level) f WHERE jl.grade_level <> '' GROUP BY f.grade_level ORDER BY CASE f.grade_level WHEN 'Elementary' THEN 1 WHEN 'Middle' THEN 2 WHEN 'High' THEN 3 END
For every expert, there is an equal and opposite expert.  Becker's Law
Naomi Nosonovsky, Sr. ProgrammerAnalyst
My blog
 Marked as answer by Kalman Toth Tuesday, March 29, 2011 7:06 PM
 Edited by Naomi N Wednesday, March 30, 2011 1:54 PM
Tuesday, March 29, 2011 5:18 PM 
WITH cte AS (SELECT CASE WHEN jl.grade_level IN ( 'PreK','K','1','2','3','4','K4' ) THEN 'Elementary' WHEN jl.grade_level IN ( '58','5','6','7','8' ) THEN 'Middle' WHEN jl.grade_level IN ( '9','10','11','12','912','High') THEN 'High' END as grade_level FROM JobList jl WHERE jl.grade_level <> '') SELECT grade_level, COUNT(grade_level) AS NumJobsByGrade FROM cte GROUP BY grade_level ORDER BY CASE WHEN grade_level = 'Elementary' THEN 1 WHEN grade_level = 'Middle' THEN 2 WHEN grade_level = 'HIGH' THEN 3 END
Tom Marked as answer by Westside2008 Tuesday, March 29, 2011 5:31 PM
Tuesday, March 29, 2011 5:24 PM
All replies

You can in this particular case repeat the exact same expression in the ORDER BY or alternatively give a different name to the resulting column (Say, GradeLevel) and use this alias or use ordinal position.
BTW, for your amusement and to be aware of possible problems I suggest this blog post
The Troll's Puzzle  a SQL fable
Update, I see you want to order them not in the character order. You need to use
SELECT f.grade_level, Count(jl.grade_level) AS numjobsbygrade FROM joblist jl CROSS APPLY (SELECT CASE WHEN jl.grade_level IN ('PreK','K','1','2', '3','4','K4') THEN 'Elementary' WHEN jl.grade_level IN ('58','5','6','7', '8') THEN 'Middle' WHEN jl.grade_level IN ('9','10','11','12', '912','High') THEN 'High' END AS grade_level) f WHERE jl.grade_level <> '' GROUP BY f.grade_level ORDER BY CASE f.grade_level WHEN 'Elementary' THEN 1 WHEN 'Middle' THEN 2 WHEN 'High' THEN 3 END
For every expert, there is an equal and opposite expert.  Becker's Law
Naomi Nosonovsky, Sr. ProgrammerAnalyst
My blog
 Marked as answer by Kalman Toth Tuesday, March 29, 2011 7:06 PM
 Edited by Naomi N Wednesday, March 30, 2011 1:54 PM
Tuesday, March 29, 2011 5:18 PM 
Can you provide an example using the code I provided? I think I know what you mean but not sure how to do this syntax wise.
Tuesday, March 29, 2011 5:20 PM 
WITH cte AS (SELECT CASE WHEN jl.grade_level IN ( 'PreK','K','1','2','3','4','K4' ) THEN 'Elementary' WHEN jl.grade_level IN ( '58','5','6','7','8' ) THEN 'Middle' WHEN jl.grade_level IN ( '9','10','11','12','912','High') THEN 'High' END as grade_level FROM JobList jl WHERE jl.grade_level <> '') SELECT grade_level, COUNT(grade_level) AS NumJobsByGrade FROM cte GROUP BY grade_level ORDER BY CASE WHEN grade_level = 'Elementary' THEN 1 WHEN grade_level = 'Middle' THEN 2 WHEN grade_level = 'HIGH' THEN 3 END
Tom Marked as answer by Westside2008 Tuesday, March 29, 2011 5:31 PM
Tuesday, March 29, 2011 5:24 PM 
SELECT
CASE
WHEN jl.grade_level IN ( 'PreK','K','1','2','3','4','K4' ) THEN 'Elementary'
WHEN jl.grade_level IN ( '58','5','6','7','8' ) THEN 'Middle'
WHEN jl.grade_level IN ( '9','10','11','12','912','High') THEN 'High'
END as grade_level,
COUNT(jl.grade_level) AS NumJobsByGrade
FROM JobList jl
WHERE jl.grade_level <> ''GROUP BY CASE
WHEN jl.grade_level IN ( 'PreK','K','1','2','3','4','K4' ) THEN 'Elementary'
WHEN jl.grade_level IN ( '58','5','6','7','8' ) THEN 'Middle'
WHEN jl.grade_level IN ( '9','10','11','12','912','High') THEN 'High'
END
ORDER BY CASE
WHEN jl.grade_level IN ( 'PreK','K','1','2','3','4','K4' ) THEN 1
WHEN jl.grade_level IN ( '58','5','6','7','8' ) THEN 2
WHEN jl.grade_level IN ( '9','10','11','12','912','High') 3
If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer. DVRTuesday, March 29, 2011 5:25 PM 
I seem to get this SQL error:
Column "JobList.grade_level" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Any idea, I think I'm close here....
Tuesday, March 29, 2011 5:28 PM 
This worked Tom, I'd still like to figure out how to do it using the code I originally had because I'm sure it's possible, but your code worked fine.
WestSide2008
Tuesday, March 29, 2011 5:31 PM 
Yes, that worked as well... I will have to learn about using CROSS APPLY and CTE's. They seem powerful.Tuesday, March 29, 2011 5:38 PM