How to Order Database Records by Multiple Values in a Specific Sequence?

Multi-valued sorting of database records in a specific order

Suppose you have a table with an indexed key and a non-indexed field x_field. You need to find all records with a specific value and return them, and sort the results based on multiple values ​​in a specific order.

For example, if you have the following table:

id	x_field
123	a
124	a
125	a
126	b
127	f
128	b
129	a
130	x
131	x
132	b
133	p
134	p
135	i

and you want to sort the results in the following order, where the order is x_field = 'f', 'p', 'i', 'a':

id	x_field
127	f
133	p
134	p
135	i
123	a
124	a
125	a
129	a

You initially tried using the following query:

<code class="language-sql">SELECT *
FROM table
WHERE id NOT IN (126)
ORDER BY x_field 'f', 'p', 'i', 'a'</code>

However, this query returns no results.

The way to solve this problem is to use the CASE statement to assign a numeric value to each x_field value according to the desired order:

<code class="language-sql">...
WHERE
   x_field IN ('f', 'p', 'i', 'a') ...
ORDER BY
   CASE x_field
      WHEN 'f' THEN 1
      WHEN 'p' THEN 2
      WHEN 'i' THEN 3
      WHEN 'a' THEN 4
      ELSE 5 -- 对不在 IN 子句中的值（例如：x_field = 'b'）的回退值
   END, id</code>

This query assigns the value 1 to the x_field value equal to 'f', the value 2 to the value equal to 'p', the value 3 to the value equal to 'i', and the value 4 to Equal to the value of 'a'. For values ​​not in the IN clause (e.g. 'b'), a fallback value of 5 is assigned. The results are then sorted in ascending order based on this numeric value and the id field.

This approach ensures that the results are sorted in the desired order, even if x_field the values ​​are not in descending/ascending order.

The above is the detailed content of How to Order Database Records by Multiple Values in a Specific Sequence?. For more information, please follow other related articles on the PHP Chinese website!