Implementation of MySQL custom list sorting by specified field

Implementation of MySQL custom list sorting by specified field

Problem Description

As we all know, the SQL to sort in ascending order by a field in MySQL is (taking id as an example, the same below):

SELECT * FROM `MyTable`
WHERE `id` IN (1, 7, 3, 5)
ORDER BY `id` ASC

The SQL for descending order is:

SELECT * FROM `MyTable`
WHERE `id` IN (1, 7, 3, 5)
ORDER BY `id` DESC

Sometimes the above sorting does not meet our needs. For example, we want to sort by id in the order of 5, 3, 7, 1, how to achieve it? This is also one of the problems often encountered by many domestic and foreign counterparts.

Below we give a solution to sort a field in the table in the list format we want.

Solution

Use "ORDER BY FIELD".

grammar

ORDER BY FIELD(`id`, 5, 3, 7, 1)

Note that there is no space after FIELD.

Therefore, the complete SQL is:

SELECT * FROM `MyTable`
WHERE `id` IN (1, 7, 3, 5)
ORDER BY FIELD(`id`, 5, 3, 7, 1)

Common Applications

SELECT * FROM `MyTable`
WHERE `name` IN ('张三', '李四', '王五', '孙六')
ORDER BY FIELD(`name`, '李四', '孙六', '张三', '王五')

The above article on how to implement MySQL custom list sorting by specified fields is all I want to share with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.

You may also be interested in:
  • Mysql method to copy a column of data in one table to a column in another table
  • MySQL FAQ series: When to use temporary tables
  • Sql query MySql database table name and description table field (column) information
  • An example of how to use Java+MySQL recursion to concatenate tree-shaped JSON lists
  • Summary of Mysql table, column, database addition, deletion, modification and query problems
  • How to get the field list after querying the results of the Python module pymysql
  • MySQL table and column comments summary

<<:  Detailed explanation of Vite's new experience

>>:  How to use vite to build vue3 application

Recommend

How to resize partitions in CentOS7

Yesterday, I helped someone install a system and ...

mysql three tables connected to create a view

Three tables are connected. Field a of table A co...

An example of vertical centering of sub-elements in div using Flex layout

1. Flex is the abbreviation of Flexible Box, whic...

html opens a new window with a hyperlink and can control window properties

1. The window size opened by the HTML hyperlink C...

MySQL slow query method and example

1. Introduction By enabling the slow query log, M...

How to create a stored procedure in MySQL and add records in a loop

This article uses an example to describe how to c...

How to support Webdings fonts in Firefox

Firefox, Opera and other browsers do not support W...

Vue batch update dom implementation steps

Table of contents Scene Introduction Deep respons...

MySQL deduplication methods

MySQL deduplication methods 【Beginner】There are v...

Vue implements internationalization of web page language switching

1. Basic steps 1: Install yarn add vue-i18n Creat...

Vue Learning - VueRouter Routing Basics

Table of contents 1. VueRouter 1. Description 2. ...