MySQL query to order rows with value greater than zero?

Let us first create a table. Following is the query −

mysql> create table gettingAndOrderingRowsDemo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> Value int
   -> );
Query OK, 0 rows affected (1.35 sec)

Following is the query to insert some records in the table using insert command −

mysql> insert into gettingAndOrderingRowsDemo(Value) values(10);
Query OK, 1 row affected (0.33 sec)

mysql> insert into gettingAndOrderingRowsDemo(Value) values(13);
Query OK, 1 row affected (0.32 sec)

mysql> insert into gettingAndOrderingRowsDemo(Value) values(0);
Query OK, 1 row affected (0.17 sec)

mysql> insert into gettingAndOrderingRowsDemo(Value) values(20);
Query OK, 1 row affected (0.26 sec)

mysql> insert into gettingAndOrderingRowsDemo(Value) values(30);
Query OK, 1 row affected (0.50 sec)

mysql> insert into gettingAndOrderingRowsDemo(Value) values(60);
Query OK, 1 row affected (0.24 sec)

mysql> insert into gettingAndOrderingRowsDemo(Value) values(0);
Query OK, 1 row affected (0.28 sec)

mysql> insert into gettingAndOrderingRowsDemo(Value) values(45);
Query OK, 1 row affected (0.15 sec)

mysql> insert into gettingAndOrderingRowsDemo(Value) values(0);
Query OK, 1 row affected (0.69 sec)

Following is the query to display all records from the table using select statement −

mysql> select * from gettingAndOrderingRowsDemo;

This will produce the following output −

+----+-------+
| Id | Value |
+----+-------+
| 1  | 10    |
| 2  | 13    |
| 3  | 0     |
| 4  | 20    |
| 5  | 30    |
| 6  | 60    |
| 7  | 0     |
| 8  | 45    |
| 9  | 0     |
+----+-------+
9 rows in set (0.00 sec)

Following is the query to order with a value greater than zero −

mysql> select * from gettingAndOrderingRowsDemo
-> order by Value=0,Value;

This will produce the following output −

+----+-------+
| Id | Value |
+----+-------+
| 1  | 10    |
| 2  | 13    |
| 4  | 20    |
| 5  | 30    |
| 8  | 45    |
| 6  | 60    |
| 3  | 0     |
| 7  | 0     |
| 9  | 0     |
+----+-------+
9 rows in set (0.00 sec)

Anvi Jain

Published on 15-Apr-2019 13:42:29

• Related Questions & Answers
• MongoDB query to match documents with array values greater than a specific value
• MySQL query to select rows older than a week?
• How to hide table rows containing zero value with jQuery?
• MySQL query to delete all rows older than 30 days?
• How do I write not greater than in a MySQL query?
• MySQL query to fetch date records greater than the current date after adding days with INTERVAL?
• MySQL query to decrease the value of a specific record to zero?
• MySQL query to select rows except first row in descending order?
• MySQL query to find a value appearing more than once?
• MySQL query to return TRUE for rows having positive value?
• Count number of substrings with numeric value greater than X in C++
• MySQL query to find all rows where string contains less than four characters?
• Show row with zero value after addition in MySQL?
• MySQL query to first set negative value in descending order and then positive value in ascending order
• How to insert multiple rows with single MySQL query?