Re: Ordering by a complex field

In article <e09785e00707181812l628f1634j163a4190111dc73a(at)mail(dot)gmail(dot)com>,
Robert James <srobertjames(at)gmail(dot)com> wrote:

% I'd like to order so that records where field='2' come first, then '1', then
% '9', then anything but '0', then '0'. Is there anyway to do this in a
% standard order by clause (that is, without writing a new SQL function)?

You can use a case statement in the order by clause

order by case when field = '0' then 4
when field = '1' then 1
when field = '2' then 0
when field = '9' then 2
else 3
end
--

Patrick TJ McPhee
North York Canada
ptjm(at)interlog(dot)com