[RESOLVED] designating an unnatural order

[gopanthers]

I'm a newbie but this code below should simply select all and order it first by the numeric "year" field, and within each result with the same year sub-order it alphabetically by the "lastname" field.

$query = "SELECT * FROM table ORDER BY year,lastname";

But what I need to do is sort things by a non-numeric and non-alphabetic order.

One option could be like this:

1. "Spring 2002"

2. "Fall 2002"

3. "Spring 2003"

4. "Fall 2003"

You see how (without changing the data itself) this has an intended order but couldn't be ordered alphabetically.

Another option (and the one I'm trying to do right now) could be like this:

1. "Alpha"

2. "Beta"

3. "Gamma"

4. "Delta"

5. "Zeta"

6. "Eta"

Here you can see how this has an intended order (greek alphabet spelled out) but couldn't be ordered alphabetically.

So is there a way that I can pre-define the order to be used in the mySQL query? Is there a way for me to say Alpha=1, Beta=2, Gamma=3, and so forth and have my query order it my way? Instead of the code I used at the top, I'd like it to work kind of like this...

$query = "SELECT * FROM table ORDER BY my_designated_order_for_fieldname,lastname";

but how?

[Lars_Berg]

select *
  from t
 order by case c when 'Alpha' then 1 when 'Beta' then 2 ... end

[gopanthers]

I tried that solution but now I get no results (no error message).

$query = "SELECT * FROM tablename ORDER BY greek_letters when 'Alpha' then 1 when 'Beta' then 2 when 'Gamma' then 3 when 'Delta' then 4 when 'Zeta' then 5 when 'Eta' then 6";
$result = mysql_query ($query);

What am I doing wrong?

[bradgrafelman]

Your syntax is wrong - look at Lars' post above; you're missing an important keyword.

Also, there's no error message because you never check for an error. If [man]mysql_query/man returns false (hint: you should be checking for this), then you can use [man]mysql_error/man to retrieve the last error message returned by MySQL for debugging purposes.

[gopanthers]

Ah ha. Now I got it working.

$query = "SELECT * FROM tablename ORDER BY CASE greek_letters
	when 'Alpha' then 1 
	when 'Beta' then 2
	when 'Gamma' then 3 
	when 'Delta' then 4
	when 'Epsilon' then 5 
	when 'Zeta' then 6
	when 'Eta' then 7
	END, lastname"; 

Thanks for the help, all.