

- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Using MySQL, can I sort a column but allow 0 to come last?
You can sort a column, with 0 come last with the help of ORDER BY. The syntax is as follows −
select *from yourTableName order by yourFieldName = 0,yourFieldName;
To understand the above concept, let us create a table. The query to create a table is as follows −
mysql> create table SortColumnZeroAtLastDemo −> ( −> RankNumber int −> ); Query OK, 0 rows affected (1.40 sec)
Now you can insert records in the table using the following query −
mysql> insert into SortColumnZeroAtLastDemo values(100); Query OK, 1 row affected (0.20 sec) mysql> insert into SortColumnZeroAtLastDemo values(0); Query OK, 1 row affected (0.17 sec) mysql> insert into SortColumnZeroAtLastDemo values(0); Query OK, 1 row affected (0.11 sec) mysql> insert into SortColumnZeroAtLastDemo values(50); Query OK, 1 row affected (0.13 sec) mysql> insert into SortColumnZeroAtLastDemo values(10); Query OK, 1 row affected (0.15 sec) mysql> insert into SortColumnZeroAtLastDemo values(0); Query OK, 1 row affected (0.17 sec) mysql> insert into SortColumnZeroAtLastDemo values(40); Query OK, 1 row affected (0.12 sec)
Display all records from the table with the help of select statement. The query is as follows −
mysql> select *from SortColumnZeroAtLastDemo;
The following is the output −
+------------+ | RankNumber | +------------+ | 100 | | 0 | | 0 | | 50 | | 10 | | 0 | | 40 | +------------+ 7 rows in set (0.00 sec)
Here is the query to sort column and set value 0 to be at last using the syntax we discussed in the beginning −
mysql> select *from SortColumnZeroAtLastDemo order by RankNumber = 0,RankNumber;
The following is the output −
+------------+ | RankNumber | +------------+ | 10 | | 40 | | 50 | | 100 | | 0 | | 0 | | 0 | +------------+ 7 rows in set (0.00 sec)
- Related Questions & Answers
- How do I modify a MySQL column to allow NULL?
- How do I update the decimal column to allow more digits in MySQL?
- Can I get Age using BirthDate column in a MySQL query?
- How can I order in group but randomly with MySQL?
- How can I return 0 for NULL in MySQL?
- How can I tell when a MySQL table was last updated?
- How can we allow MySQL to store invalid dates?
- Can you allow a regex match in a MySQL Select statement?
- How can I set 0 if a query returns a null value in MySQL?
- Can I search for particular numbers in a MySQL column with comma separated records using a MySQL query?
- How can I drop an existing column from a MySQL table?
- How can I remove every column in a table in MySQL?
- MySQL select to count values equal to 0 and greater than 0 from a column?
- MySQL query to sort by certain last string character?
- How can I change the default sort order of MySQL tables?
Advertisements