Sort certain values to the top in MySQL?

You need to use ORDER BY clause to sort. The syntax is as follows −

SELECT *FROM yourTableName ORDER BY yourColumnName='yourValue' DESC,yourIdColumnName;

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table SortCertainValues
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> Name varchar(20),
   -> CountryName varchar(10),
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (1.36 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into SortCertainValues(Name,CountryName) values('Adam','US');
Query OK, 1 row affected (0.35 sec)
mysql> insert into SortCertainValues(Name,CountryName) values('John','UK');
Query OK, 1 row affected (0.74 sec)
mysql> insert into SortCertainValues(Name,CountryName) values('Bob','US');
Query OK, 1 row affected (0.20 sec)
mysql> insert into SortCertainValues(Name,CountryName) values('Carol','Denmark');
Query OK, 1 row affected (0.32 sec)
mysql> insert into SortCertainValues(Name,CountryName) values('Sam','US');
Query OK, 1 row affected (0.19 sec)
mysql> insert into SortCertainValues(Name,CountryName) values('David','France');
Query OK, 1 row affected (0.13 sec)
mysql> insert into SortCertainValues(Name,CountryName) values('Maxwell','AUS');
Query OK, 1 row affected (0.25 sec)
mysql> insert into SortCertainValues(Name,CountryName) values('Kevin','NewZealand');
Query OK, 1 row affected (0.29 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from SortCertainValues;

The following is the output −

+----+---------+-------------+
| Id | Name    | CountryName |
+----+---------+-------------+
|  1 | Adam    | US          |
|  2 | John    | UK          |
|  3 | Bob     | US          |
|  4 | Carol   | Denmark     |
|  5 | Sam     | US          |
|  6 | David   | France      |
|  7 | Maxwell | AUS         |
|  9 | Kevin   | NewZealand  |
+----+---------+-------------+
8 rows in set (0.00 sec)

Here is the query to sort certain value to top. We have set the CountryName US at the top i.e. all the records with US CountryName −

mysql> select *from SortCertainValues order by CountryName='US' DESC,Id;

The following is the output −

+----+---------+-------------+
| Id | Name    | CountryName |
+----+---------+-------------+
|  1 | Adam    | US          |
|  3 | Bob     | US          |
|  5 | Sam     | US          |
|  2 | John    | UK          |
|  4 | Carol   | Denmark     |
|  6 | David   | France      |
|  7 | Maxwell | AUS         |
|  9 | Kevin   | NewZealand  |
+----+---------+-------------+
8 rows in set (0.00 sec)

Samual Sam

Published on 27-Feb-2019 07:39:29

Previous Page Print Page