How to order by custom rule, e.g. how to order like 4,2,1,3

Question

I am not sure that the title is saying it right, most probably it does not :)

So I have products table and I want to order them by the season they are made for.

If 'spring' is 1, 'summer' - 2 , 'autumn' - 3 and 'winter' - 4, how can I order them in such way so it shows the 'summer' first, then 'spring', then 'winter' and at the end 'autumn'. So like 2,1,4,3.

And to clarify it more, I want to be able to change the rule. It might be 2,1,4,3 or 4,2,1,3, etc. This is to show the season products on top.

Hope that explanation helps to get the problem. Any solutions are welcomed. Database is MySQL, language PHP.

Answer 1

Create a string of "season" ids. Then do the order by field thing:

SELECT * FROM foo ORDER BY FIELD(season_id, 4, 2, 1, 3);

Replace the 4, 2, 1, 3 part with interpolated php variable.

Answer 2

This should work for all major dbms:

order 
   by case when season = 2 then 1 
           when season = 1 then 2
           when season = 4 then 3
           when season = 3 then 4 
       end;

You could also add a column to your season table to indicate how they should be sorted:

table season(
   season_id int
  ,ordinal   int 
);


select ...
  from tab t
  join seasons s on(t.season_id = s.season_id)
 order 
    by s.ordinal;

...or, if you feel like writing non-portable code you can use:

order by field(season_id, 2,1,4,3);