Order by a single field and display rest of the records in the same order with MySQL

Let us first create a table −

mysql> create table DemoTable
   -> (
   -> Id int,
   -> Name varchar(20)
   -> );
Query OK, 0 rows affected (0.82 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values(201,'Chris Brown');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable values(110,'John Doe');
Query OK, 1 row affected (0.29 sec)
mysql> insert into DemoTable values(101,'Adam Smith');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values(345,'Carol Taylor');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values(135,'John Smith');
Query OK, 1 row affected (0.11 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+------+--------------+
| Id   | Name         |
+------+--------------+
|  201 | Chris Brown  |
|  110 | John Doe     |
|  101 | Adam Smith   |
|  345 | Carol Taylor |
|  135 | John Smith   |
+------+--------------+
5 rows in set (0.00 sec)

Here is the query to ORDER By field. The Id 101 would remain on the top and rest of the records would get displayed in the same order −

mysql> select *from DemoTable
   -> order by (Id=101) desc;

This will produce the following output −

+------+--------------+
| Id   | Name         |
+------+--------------+
| 101  | Adam Smith   |
| 201  | Chris Brown  |
| 110  | John Doe     |
| 345  | Carol Taylor |
| 135  | John Smith   |
+------+--------------+
5 rows in set (0.00 sec)

AmitDiwan

Published on 12-Dec-2019 07:43:54

• Related Questions & Answers
• Order MySQL records randomly and display name in Ascending order
• How to ORDER BY FIELD with GROUP BY in a single MySQL query?
• How to use ORDER BY field and sort by id in a single MySQL field?
• How to ORDER BY DESC and display the first 3 records in MySQL?
• MySQL ORDER BY with custom field value
• MySQL order by 0 first and then display the record in descending order?
• MySQL query to order records but fix a specific name and display rest of the values (only some) random
• Display the count of duplicate records from a column in MySQL and order the result
• Display records from the current date till rest of the same month in MySQL?
• Fix a row value and then ORDER BY DESC rest of the values in MySQL
• MySQL ORDER BY ASC and display NULLs at the bottom?
• How to display records having sum between a specific range using GROUP BY, HAVING and ORDER BY in a single MySQL query?
• How to sum the score of students with the same name in MySQL with ORDER BY?
• MySQL Order by a specific column x and display remaining values in ascending order
• How to order records by a column in MySQL and place empty records at the end?