CODEWITHSUNDEEP
mysqlsql
sod
sod

Reputation: 3928

SQL ORDER BY multiple fields

I have this query:

SELECT
  id, name
FROM
  data
ORDER BY
  FIELD(id, 4, 5, 8, 10) DESC,
  name

Ids 4, 5, 8, 10 are up top. So far so good. But then I want to sort by name. This doesn't work. Did maybe FIELD already determined a specific order? Can I change the SQL so I can still tell SQL what ids should be up top (in a true/falsy relation) but do the final ordering by name? Some kind of enum?

The ids in FIELD(id, 4, 5, 8, 10) came from another source. They represent if there is data to given id in the foreign source.

Upvotes: 0

Views: 101

Answers (1)

ughai
ughai

Reputation: 9880

you can use ANSI 92 Compatible CASE WHEN like this.

SELECT
  id, name
FROM
  data
ORDER BY
  CASE WHEN id IN (4, 5, 8, 10) THEN 0 ELSE 1 END ASC,
  name

Upvotes: 5

Related Questions