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

转载于 2010-11-08 15:18:00 发布 · 575 阅读

文章标签：

#postgresql #sorting #application #rails #sql

本文探讨了如何在PostgreSQL中模拟MySQL的FIELD函数排序行为。通过使用CASE WHEN语句实现特定顺序排序，并保持其他字段的正常排序逻辑。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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;