Skip to main content
M
Mr_Vball
Participating Frequently
August 18, 2008
Question

Forcing a specific result set order

  • August 18, 2008
  • 3 replies
  • 454 views
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
This topic has been closed for replies.

3 replies

D
Dan_Bracuk
Inspiring
August 20, 2008
It should be in your select clause, not your order by clause.
D
Dan_Bracuk
Inspiring
August 18, 2008
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.
M
Mr_VballAuthor
Participating Frequently
August 19, 2008
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?


N
Newsgroup_User
Inspiring
August 18, 2008

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.
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices