I have a field called "type" in my "school" table that can have three
possibilities:

Elementary
Middle
High

In my result set, I always want Elementary to come first, Middle to
come second, and High to come third. An alpha sort gets me this:

Elementary
High
Middle

Sure, I could create a look-up that assigns an integer to the "type"
field to get the right sort order (i.e. elementary = 0, middle = 1,
high = 2) ... but is there a better way to do this with SQL syntax?

Thanks,

Ralph Noble
ralph_noble@hotmail.com

On 1 Nov 2004 11:23:53 -0800, Ralph Noble wrote:

>In my result set, I always want Elementary to come first, Middle to
>come second, and High to come third.
(...)
> ... but is there a better way to do this with SQL syntax?

Hi Ralph,

ORDER BY CASE Type
WHEN 'Elementary' THEN 1
WHEN 'Middle' THEN 2
WHEN 'High' THEN 3
ELSE NULL
END

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)