Re: Ordering by a complex field - Mailing list pgsql-general

In article <e09785e00707181812l628f1634j163a4190111dc73a@mail.gmail.com>,
Robert James <srobertjames@gmail.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@interlog.com