MySQL query to order rows with value greater than zero?

MySQL Database Training for Beginners

39 Lectures 5.5 hours

Simon Sez IT

More Detail

Python programming with MySQL database: from Scratch

152 Lectures 16 hours

Metla Sudha Sekhar

More Detail

Learn MySQL from scratch for Data Science and Analytics

87 Lectures 5.5 hours

Metla Sudha Sekhar

More Detail

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

Updated on 30-Jul-2019 22:30:25

• 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?
• MySQL query to delete all rows older than 30 days?
• How to hide table rows containing zero value with jQuery?
• 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 select rows except first row in descending order?
• MySQL query to decrease the value of a specific record to zero?
• MySQL query to find a value appearing more than once?
• MySQL query to return TRUE for rows having positive value?
• MySQL query to find all rows where string contains less than four characters?
• How to insert multiple rows with single MySQL query?
• MySQL query to count rows with a specific column?
• MySQL query to first set negative value in descending order and then positive value in ascending order
• Count number of substrings with numeric value greater than X in C++