Forcing a specific result set order

[Mr_Vball]

I have a query that looks like this
SELECT DISTINCT isSchool_Type + ' (' + CAST(COUNT(1) AS varchar) + ')' AS navtitle, COUNT(1) AS k, null as locate1, null as pageid, 'schools.cfm?schooltype=' + CAST(isSchool_Type AS varchar) + '&' AS thepage
FROM Locations
WHERE (isSchool = 1) AND (isSchool_Type IS NOT NULL)
GROUP BY isSchool_Type
ORDER BY navtitle

isSchool_type can be high, intermediate, middle or elementary. The problem is that I want the result set to be ordered in a specific order (Not alphabetically like it is now.) Is there a way to do this right in the ORDER BY CLAUSE of the SQL statement without having to create a "sortby" column and putting the numbers in.

TIA

[Newsgroup_User]

What order do you want it sorted in? There maybe something that can be
done to create virtual columns that can be used to sort the results.

[Dan_Bracuk]

If your db supports it, you can create that sortby column in your query using a case contstruct.

You could also put your school_types in a different table and have your sortby column there. Then it's only 5 records and you don't have to worry about it when adding new records to your locations table.

[Mr_Vball]

quote:

---

Originally posted by: Dan Bracuk
If your db supports it, you can create that sortby column in your query using a case contstruct.

You could also put your school_types in a different table and have your sortby column there. Then it's only 5 records and you don't have to worry about it when adding new records to your locations table.

---

Sorry - I should have clarified in my OP.
We are using SQL Server 2005 (so the Field property that mysql has is out). I tryed using a CASE statement but it kept giving me a syntax error. I had
....
ORDER BY
(CASE navtitle
WHEN 'elementary' THEN 1
WHEN 'high' THEN 2
...
END)

Given the way things are setup and the "sharing" of the table with several clients - using a seperate sort column is kind of a "last resort" type thing as it would be more difficult to implement than it appears. Can you see the issue with my case stmt?

[Dan_Bracuk]

It should be in your select clause, not your order by clause.