Simulating MySQL's ORDER BY FIELD() in Postgresql

Question

Hello all,

Just trying out Postgresql for the first time, coming from MySQL. In our Rails application we have a couple of locations with SQL like so:

SELECT * FROM `currency_codes` ORDER BY FIELD(code, 'GBP', 'EUR', 'BBD', 'AUD', 'CAD', 'USD') DESC, name ASC

It didn't take long to discover that this is not supported/allowed in Postgresql.

Does anyone know how to simulate this behaviour in Postgres or do we have to pull to sorting out into the code?

Thanks

Peer

Answer 1

You can do this...

SELECT 
   ..., code
FROM 
   tablename
ORDER BY 
   CASE 
      WHEN code='GBP' THEN 1
      WHEN code='EUR' THEN 2
      WHEN code='BBD' THEN 3
      ELSE 4
   END

But why are you hardcoding these into the query -- wouldn't a supporting table be more appropriate?

--

Edit: flipped it around as per comments

Answer 2

Update, fleshing out terrific suggestion by @Tometzky.

This ought to give you a MySQL FIELD()-alike function under pg 8.4:

-- SELECT FIELD(varnames, 'foo', 'bar', 'baz')
CREATE FUNCTION field(anyelement, VARIADIC anyarray) RETURNS numeric AS $$
  SELECT
    COALESCE(
     ( SELECT i FROM generate_subscripts($2, 1) gs(i)
       WHERE $2[i] = $1 ),
     0);
$$ LANGUAGE SQL STABLE

Mea culpa, but I cannot verify the above on 8.4 right now; however, I can work backwards to a "morally" equivalent version that works on the 8.1 instance in front of me:

-- SELECT FIELD(varname, ARRAY['foo', 'bar', 'baz'])
CREATE OR REPLACE FUNCTION field(anyelement, anyarray) RETURNS numeric AS $$
  SELECT
    COALESCE((SELECT i
              FROM generate_series(1, array_upper($2, 1)) gs(i)
              WHERE $2[i] = $1),
             0);
$$ LANGUAGE SQL STABLE

More awkwardly, you still can portably use a (possibly derived) table of currency code rankings, like so:

pg=> select cc.* from currency_codes cc
     left join
       (select 'GBP' as code, 0 as rank union all
        select 'EUR', 1 union all
        select 'BBD', 2 union all
        select 'AUD', 3 union all
        select 'CAD', 4 union all
        select 'USD', 5) cc_weights
     on cc.code = cc_weights.code
     order by rank desc, name asc;
 code |           name
------+---------------------------
 USD  | USA bits
 CAD  | Canadian maple tokens
 AUD  | Australian diwallarangoos
 BBD  | Barbadian tridents
 EUR  | Euro chits
 GBP  | British haypennies
(6 rows)

Answer 3

Ah, gahooa was so close:

SELECT * FROM currency_codes
  ORDER BY
  CASE
    WHEN code='USD' THEN 1
    WHEN code='CAD' THEN 2
    WHEN code='AUD' THEN 3
    WHEN code='BBD' THEN 4
    WHEN code='EUR' THEN 5
    WHEN code='GBP' THEN 6
    ELSE 7
  END,name;

Answer 4

If you'll run this often, add a new column and a pre-insert/update trigger. Then you set the value in the new column based on this trigger and order by this field. You can even add an index on this field.

Answer 5

This is I think the simplest way:

create temporary table test (id serial, field text);
insert into test(field) values
  ('GBP'), ('EUR'), ('BBD'), ('AUD'), ('CAD'), ('USD'),
  ('GBP'), ('EUR'), ('BBD'), ('AUD'), ('CAD'), ('USD');
select * from test
order by field!='GBP', field!='EUR', field!='BBD',
  field!='AUD', field!='CAD', field!='USD';
 id | field 
----+-------
  1 | GBP
  7 | GBP
  2 | EUR
  8 | EUR
  3 | BBD
  9 | BBD
  4 | AUD
 10 | AUD
  5 | CAD
 11 | CAD
  6 | USD
 12 | USD
(12 rows)

In PostgreSQL 8.4 you can also use a function with variable number of arguments (variadic function) to port field function.

Answer 6

Actually the version for postgres 8.1 as another advantage.

When calling a postgres function you cannot pass more than 100 parameters to it, so your ordering can be done at maximum on 99 elements.

Using the function using an array as second argument instead of having a variadic argument just remove this limit.