- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
- Related Articles
- MySQL query to sort by certain last string character?
- How to select the top two values using LIMIT in MySQL?
- MySQL query to sort column values and ignoring quotes on one of the values
- MySQL query to select column values ending with certain character/number?
- MySQL query to sum up values of rows and sort the result?
- Count top 10 most occurring values in a column in MySQL?
- How can you update certain values in a table in MySQL using Python?
- Selecting the top occurring entries in MySQL from a table with duplicate values?
- Sort by order of values in a MySQL select statement IN clause?
- Sort values that contain letters and symbols in custom order with MySQL
- Sort character values from a column mixed with character and numbers in MySQL?
- How to sort a boxplot by the median values in Pandas?
- How to sort domain names in MySQL?
- How to sort Map values by key in Java?
- How to sort a dictionary in Python by values?

Advertisements