MariaDB ORDER BY clause


Objectives

  • Understanding of MariaDB ORDER BY Clause

  • MariaDB ORDER BY ASC | Ascending

  • MariaDB ORDER BY DESC | Descending

  • MariaDB ORDER BY Multiple Column


Create database and table and insert data if it is not created already

--- Create database if not exist
CREATE DATABASE IF NOT EXISTS mariadb_tutorial;

--- Select the database for further operation
USE mariadb_tutorial;

--- Create table if not exist
CREATE TABLE IF NOT EXISTS person (
    id int(12) NOT NULL AUTO_INCREMENT,
    first_name varchar(150) NOT NULL,
    last_name varchar(150),
    email varchar(100),
    age int,
    income double,
    PRIMARY KEY (id)
)

--- Remove old Data
TRUNCATE person;

--- Insert data into table if not exist
INSERT INTO person (first_name, last_name, email, age, income)
VALUES
	('Faiyaz', 'Mia', 'faiyaz@pf.local', 1, 5000),
	('John', 'Doe', 'john@pf.local', 19, 100),
	('Tahsin', NULL, 'tahsin@pf.local', 10, 150),
	('Jane', 'Doe', 'jane@gmail.com', 26, 300),
	('Rakib', 'Mia', 'rakib@bf.local', 24, 200),
	('Sagor', 'Sowrov', 'email10@bf.local', 20, 250),
	('Touhid', NULL, 'hmtmcse.com@gmail.com', 30, 500);


Understanding of MariaDB ORDER BY Clause

The ORDER BY keyword is used to sort the result-set in ascending or descending order. Suppose to you need a result set where age should be ascending order or lowest to highest or vice versa, this types of situation can use ORDER BY


MariaDB ORDER BY ASC | Ascending

Problem: Find person according age ascending order

Syntax

SELECT * FROM table_name ORDER BY column1, column2, ... ASC;

Example

SELECT * FROM person ORDER BY age ASC;

Output

+----+------------+-----------+-----------------------+------+--------+
| id | first_name | last_name | email                 | age  | income |
+----+------------+-----------+-----------------------+------+--------+
|  1 | Faiyaz     | Mia       | faiyaz@pf.local       |    1 |   5000 |
|  3 | Tahsin     | NULL      | tahsin@pf.local       |   10 |    150 |
|  2 | John       | Doe       | john@pf.local         |   19 |    100 |
|  6 | Sagor      | Sowrov    | email10@bf.local      |   20 |    250 |
|  5 | Rakib      | Mia       | rakib@bf.local        |   24 |    200 |
|  4 | Jane       | Doe       | jane@gmail.com        |   26 |    300 |
|  7 | Touhid     | NULL      | hmtmcse.com@gmail.com |   30 |    500 |
+----+------------+-----------+-----------------------+------+--------+
7 rows in set (0.003 sec)


MariaDB ORDER BY DESC | Descending

Problem: Find person according age descending order

Syntax

SELECT * FROM table_name ORDER BY column1, column2, ... DESC;

Example

SELECT * FROM person ORDER BY age DESC;

Output

+----+------------+-----------+-----------------------+------+--------+
| id | first_name | last_name | email                 | age  | income |
+----+------------+-----------+-----------------------+------+--------+
|  7 | Touhid     | NULL      | hmtmcse.com@gmail.com |   30 |    500 |
|  4 | Jane       | Doe       | jane@gmail.com        |   26 |    300 |
|  5 | Rakib      | Mia       | rakib@bf.local        |   24 |    200 |
|  6 | Sagor      | Sowrov    | email10@bf.local      |   20 |    250 |
|  2 | John       | Doe       | john@pf.local         |   19 |    100 |
|  3 | Tahsin     | NULL      | tahsin@pf.local       |   10 |    150 |
|  1 | Faiyaz     | Mia       | faiyaz@pf.local       |    1 |   5000 |
+----+------------+-----------+-----------------------+------+--------+
7 rows in set (0.000 sec)


MariaDB ORDER BY Multiple Column

Syntax

SELECT * FROM table_name ORDER BY column1, column2, ... ASC|DESC;

Example

SELECT * FROM person ORDER BY age DESC, income ASC;

Output

+----+------------+-----------+-----------------------+------+--------+
| id | first_name | last_name | email                 | age  | income |
+----+------------+-----------+-----------------------+------+--------+
|  7 | Touhid     | NULL      | hmtmcse.com@gmail.com |   30 |    500 |
|  4 | Jane       | Doe       | jane@gmail.com        |   26 |    300 |
|  5 | Rakib      | Mia       | rakib@bf.local        |   24 |    200 |
|  6 | Sagor      | Sowrov    | email10@bf.local      |   20 |    250 |
|  2 | John       | Doe       | john@pf.local         |   19 |    100 |
|  3 | Tahsin     | NULL      | tahsin@pf.local       |   10 |    150 |
|  1 | Faiyaz     | Mia       | faiyaz@pf.local       |    1 |   5000 |
+----+------------+-----------+-----------------------+------+--------+
7 rows in set (0.001 sec)


MariaDB ORDER BY clause Video tutorial

Share