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

mysql security management details

Table of contents 1. Introduce according to the o...

MySQL 8.0.12 installation and configuration method graphic tutorial

Record the installation and configuration method ...

Implementation of WeChat applet message push in Nodejs

Select or create a subscription message template ...

A brief understanding of the differences between MySQL InnoDB and MyISAM

Preface MySQL supports many types of tables (i.e....

Examples of some usage tips for META tags in HTML

HTML meta tag HTML meta tags can be used to provi...

Graphic tutorial on installing CentOS7 on VMware 15.5

1. Create a new virtual machine in VMware 15.5 1....

WeChat applet records user movement trajectory

Table of contents Add Configuration json configur...

Example of usage of keep-alive component in Vue

Problem description (what is keep-alive) keep-ali...

Vue directives v-html and v-text

Table of contents 1. v-text text rendering instru...

Implementation of HTML sliding floating ball menu effect

CSS Styles html,body{ width: 100%; height: 100%; ...

How to deploy redis in linux environment and install it in docker

Installation Steps 1. Install Redis Download the ...

Deploy Confluence with Docker

1. Environmental requirements 1. Docker 17 and ab...

js, css, html determine the various versions of the browser

Use regular expressions to determine the IE browse...

js implementation of verification code case

This article example shares the specific code of ...