MariaDB Order By Clause

In this MariaDB tutorial, we will study the use of the MariaDB Order By clause and we will also cover some examples. There are lists of the topic that comes under discussion:

• MariaDB order by
• MariabB order by multiple columns
• MariaDB order by desc
• MariaDB order by field
• MariaDB order by null last
• MariaDB order by random
• MariaDB order by limit
• MariaDB order by timestamp
• MariaDB order by before group by
• MariaDB order by date
• MariaDB order by index

MariaDB Order By

In this section, we will learn how to use the MariaDB ORDER BY clause in the database. Here we will illustrate both syntax and example of MariaDB Order By.

The MariaDB ORDER BY clause is used to arrange the records in the descending or ascending order in the resultset. The following is the MariaDB syntax for the ORDER BY clause:

SYNTAX:

SELECT [EXP] FROM TBL_NAME 
WHERE [CONDITIONS]
ORDER BY [DESC | ASC];

The syntax explanation:

• expr: The column or calculation that we want to retreive.
• tbl_name: The table from which we want to retreive records from. There must be atleast one table listed in the FROM clause.
• where [conditions]: The conditions should be met from which records must be selected.
• ASC: It sorts the result in the ascending order in the expression. There is no use of ASC modifier required.
• DESC: It sorts the result in the descending order by epression.

NOTE

If there is no ASC Or DESC modifier provided in the expression then by default it uses the expression in the ascending order. This is equivalent to ORDER BY expression ASC.

First, let’s create a new USA_HP table by using the following query:

CREATE TABLE USA_HP(
HP_ID INT AUTO_INCREMENT PRIMARY KEY,
HP_NAME VARCHAR(100),
HP_DOLLARPRICE FLOAT,
HP_ORIGINALPRICE FLOAT);

INSERT INTO USA_HP(HP_NAME,HP_DOLLARPRICE,HP_ORIGINALPRICE)
VALUES('HP ENVY 17t-ch000 Laptop PC touch optional',879.99,1199.99),
('HP Laptop -15t-dw300',419.99,599.99),
('HP ENVY x360 Laptop - 15t-ed100',729.99,1049.99),
('HP Pavilion Laptop - 15t-eg100 touch optional',619.99,999.99),
('HP Laptop - 17z-cp000 touch optional',349.99,499.99),
('HP Pavilion Laptop 14-dv1097nr',769.99,879.99),
('HP ENVY Laptop - 17t-ch100',779.99,1309.99),
('HP Laptop 15z-gw000',279.99,369.99),
('HP Laptop - 15t-dy200 touch optional',579.99,749.99),
('HP ENVY Laptop - 17t-ch000 touch optional',699.99,1099.99),
('HP Pavilion Laptop - 15z-eh100',429.99,629.99),
('HP Spectre x360 Convertible Laptop - 14t-ea100',1099.99,1059.99),
('HP Laptop - 15z-ef100',299.99,419.99),
('HP Pavilion x360 Convertible 14-dy1097nr',779.999,829.99),
('HP Spectre x360 2-in-1 Laptop 16-f0035nr',1429.99,1729.99),
('HP ENVY x360 Convert 13-ay1035nr',899.99,1099.99);

SELECT * FROM USA_HP;

In the above query, we have created the USA_HP table by using the CREATE TABLE statement and inserted some records by using the INSERT TABLE statement. All the records of the USA_HP table are shown by using the SELECT statement.

The sample example of the ORDER BY clause in the MariaDB is given below:

SELECT * FROM USA_HP ORDER BY HP_NAME ASC;

Most programmers will omit the ASC attribute if the sorting is done in ascending order.

Read: MariaDB Union Operator

MariaDB Order By Multiple Columns

In this section, we will learn how to use the ORDER BY clause for multiple columns. And here we will understand how its syntax and we will also discuss an example.

Let’s first see the syntax of the ORDER BY clause as given below:

SELECT [EXPRESSIONS] FROM TBL_NAME 
WHERE [CONDITIONS]
ORDER BY COLUMN_NAME1 [DESC | ASC],
COLUMN_NAME2 [DESC | ASC];

The sample example of the MariaDB ORDER BY clause to sort a result set by two columns:

First, let us see the USA_HP table by using the SELECT statement:

SELECT * FROM USA_HP;

In this query, the SELECT statement shows all the records of the USA_HP table.

EXAMPLE: ORDER BY clause for MULTIPLE COLUMNS

SELECT HP_NAME,HP_ID FROM USA_HP
ORDER BY HP_NAME,HP_ID;

In this query, the ORDER BY clause has sorted the rows in the following sequence:

• First, it sorted the USA_HP by values in hp_id column from low to high.
• Second, it sorted USA_HP by values in the HP_NAME column alphabetically.

Read: MariaDB Select Statement

MariaDB Order By DESC

In this section, we will learn how to use the ORDER BY clause with the DESC constraint in the MariaDB with the help of syntax and examples.

The ORDER BY clause is used to sort the records in the ascending order or descending order in the result set. The basic use of the DESC constraint is that it changes the value of the expression from highest to lowest or in alphabetical order from Z alphabet to A alphabet.

The syntax of the ORDER BY clause with DESC constraint is given below:

SYNTAX:

SELECT EXPRESSIONS FROM TBL_NAME 
BETWEEN [CONDITIONS]
ORDER BY SORTED_EXPRESSION [ DESC ];

The syntax explanation:

• sorted_Expression[ desc ]: The sorted_expression refers to the column which need to be sorted in descending order based on value or string in the query.

The sample example of the SORTED BY clause with the DESC constraint is given below:

EXAMPLE:

SELECT * FROM USA_HP ORDER BY HP_NAME DESC;

In this query, the ORDER BY clause is used to retrieve all the records of the USA_HP table and the result set as HP_NAME column has been arranged in the descending order on the base of the alphabetical order from Z alphabet to A alphabet in the query.

Read: MariaDB Join with Examples

MariaDB Order By Field

In this section, we will learn how to use the field() function with the ORDER BY clause in the MariaDB with the help of syntax and examples.

In MariaDB, the field() function is used to return the index position of a comma-delimited of the value we are searching in the exists.

EXAMPLE:

SELECT * FROM usa_hp
WHERE hp_id IN (1,2,3,4)
ORDER BY IF(FIELD(hp_id,3,2,1,4)=0,1,0),FIELD(hp_id,3,2,1,4);

The sample query explanation:

• IF hp_id = 1, then FIELD(hp_id,3,2,1,4) returns 3 (position where 1 is in the list)
• IF hp_id = 2, then FIELD(hp_id,3,2,1,4) returns 2 (position where 2 is in the list)
• IF hp_id = 3, then FIELD(hp_id,3,2,1,4) returns 1 (position where 3 is in the list)
• IF hp_id = 4, then FIELD(hp_id,3,2,1,4) returns 4 (position where 4 is in the list)
• IF hp_id = anything else, then FIELD(hp_id,3,2,1,4) returns 0 (not in the list)

We have created our order.

Read: MariaDB Set Variable – Complete Guide

MariaDB Order By Null Last

In this section, we will learn how to use the NULL value at the last in the ORDER BY clause by using the following syntax and example.

In MariaDB, the NULL value is considered to be lower than any non-NULL values therefore the NULL value appears first when the order is ASC (ascending) and ordered last in the order is DESC (descending).

We’ll be discussing the two cases as sorting NULL values in either of the cases that might not be straightforward:

• Ordering NULL values last whilst sorting all Non-NULL values in ascending order.
• Ordering NULL values first followed by non NULL values in descending order.

First Sorting in MINUS operator:

In MariaDB, sorting the NULL value is considered to be lower than non-NULL values, except if a – (minus) operator is added to the column name before sorting.

SELECT * FROM USA_POSTMATES 
ORDER BY -ORDER_DATE DESC; 

In this query, we have used the minus operator with the ORDER BY clause in the USA_POSTMATES table to arrange the order_date column in descending order and give the resultset.

NOTE

While this may work for dates and numbers and it may not be best solution to sort the field with alpha or alphanumeric values. For those we want to try this other methods are given in the topic.

Using the IS NULL comparison operator:

In MariaDB, the IS NULL operator returns 1 when their expression is null otherwise it will return 0.

SYNTAX:

SELECT * FROM TBL_NAME 
ORDER BY EXPRESSIONS IS NULL,COLUMN_NAME [ DESC | ASC];

EXAMPLE:

SELECT * FROM USA_HP
ORDER BY NAME_HP IS NULL,NAME_HP DESC;

In this query, we have used the USA_HP table for the resultset and to arrange the records in order, we have to check the NAME_HP column too.

Now, if it contains an empty column or empty string in row/s then we have arranged the NAME_HP column names in descending order to get the result by the SELECT statement.

Read: MariaDB Row_Number Tutorial

MariaDB Order By Random

In this section, we will learn how to use the RANDOM as RAND() function with the ORDER BY clause in the MariaDB with the help of syntax and examples.

In MariaDB, there is no any-built function to select random rows from the table, to select random rows we just use the RAND() function. The syntax of the RAND() function with ORDER BY clause is given below:

SYNTAX:

SELECT * FROM TBL_NAME 
ORDER BY RAND() 
LIMIT limit_number;

let’s examine the query in more detail:

• The function RAND() function generates the random values from the table.
• The ORDER BY clause sorts all the rows in the table by random number generated by the RAND() function.
• The LIMIT clause picks number of rows as per user’s choice in the limit_number integer.

First, let’s have a look at the USA_HP table by using the SELECT statement:

SELECT * FROM USA_HP;

The SELECT clause will retrieve all records from the USA_HP table.

The sample example of the RAND() function with ORDER BY clause in the MariaDB is given below:

EXAMPLE:

SELECT * FROM USA_HP 
ORDER BY RAND() LIMIT 10;

In this query, the RAND() function will bring the random rows from the USA_HP table as the result and also arrange it in ascending order by using the LIMIT clause as LIMIT 10 it will bring the top 10 rows from the table with the help of SELECT statement.

Read: MariaDB Select Into + Examples

MariaDB Order By Limit

In this section, we will learn how to use the LIMIT clause with the ORDER BY clause in the MariaDB query with the help of syntax and examples.

In MariaDB, the LIMIT clause is used to retrieve records from one or more tables and limit the number of records returned based on the value.

The syntax of the LIMIT clause in the MariaDB is given below:

SYNTAX:

SELECT EXPRESSIONS FROM TBL_NAME 
WHERE [CONDITIONS]
ORDER BY expression [ DESC | ASC ]
LIMIT limit_value;

The syntax explanation:

• EXPRESSIONS: The column or caluation that we want to retreive.
• TBL_NAME: The table that we wish to retreive records from. There must be atleast one table listed in the FROM clause.
• ORDER BY expression: It uses in the SELECT LIMIT statement so that we can order the results and target those records that we want to return.
• LIMIT limit_value: It used to return the result set based on the number in the limit_value. For example- The LIMIT 10 would return first 10 values from the SELECT statement based on the query conditions. in the SELECT criteria. This is where the sort order matters so sure to use the ORDER BY clause approximately.

The sample example of the LIMIT clause in the ORDER BY clause in the MariaDB is given below:

EXAMPLE:

SELECT * FROM USA_HP 
ORDER BY HP_NAME DESC 
LIMIT 5;

In this query, the SELECT LIMIT statement would select all columns from the USA_HP table and the results are sorted in the HP_NAME column in descending order so this means that the reverse alphabetical order will be represented by using the LIMIT clause as LIMIT 5. These values will be returned by the SELECT LIMIT statement.

Read: MariaDB Regexp + Examples

MariaDB Order By Timestamp

In this section, we will learn how to use the timestamp() function with the ORDER BY clause in the MariaDB with the help of syntax and examples.

In MariaDB, it is a function that returns the date and time for every insert or update operation and the format of the TIMESTAMP is YYYY-MM-DD.

The syntax of the TIMESTAMP() function with the ORDER BY clause in the MariaDB is given below:

SELECT timestamp( theTimestampColumnName ) as new_ALIASNAME
FROM TBL_NAME
ORDER BY expression [desc |asc];

First, let’s have look at the USA_POSTMATES table by the following query:

SELECT * FROM USA_POSTMATES;

In this query, we have retrieved all records of the USA_POSTMATES table by using the SELECT statement.

The sample example of the TIMESTAMP() function with the ORDER BY clause is given below:

SELECT timestamp( order_date) as 'timestampDemo'
from usa_postmates
order by order_id ASC LIMIT 5;

In this query, the timestamp() function would give the ORDER_DATE column from the USA_POSTMATES table and arrange the order of the ORDER_ID column in ascending order.

The LIMIT clause will bring the top 5 order as the resultset as putting the LIMIT 5 in the query with the help of the SELECT statement.

Read: MariaDB on Duplicate Key Update

MariaDB Order By before Group By

In this section, we will learn how to utilize the MariaDB GROUP BY clause before the ORDER BY clause with the help of syntax and illustrated examples.

The MariaDB SELECT statement uses the GROUP BY clause to manage numerous entries and group the results in more than one column.

The syntax of the GROUP BY clause in the MariaDB is given below:

SELECT exp_1,exp_2,.... exp_n, 
agg_func (exp)
FROM <table_nam>
[WHERE conditions]
GROUP BY exp_1, exp_2,.... exp_n
ORDER BY exp [DESC | ASC];

The syntax explanation:

• exp_1,exp_2,….exp_n: The GROUP BY clause must be used for SQL expressions that are not summed in the aggregate function.
• agg_func: It stands for the aggregate function and there are some functions that can be used (MAX,SUM,MIN,COUNT and AVG) functions.
• tbl_name: The table from which data need to be retreived. In the FROM statement, there should be at least one table link.
• where [conditions]: The conditions must be met before records can be chosen.
• ORDER BY expression: The condition in which expression need to be result set based on ASC or DESC constraint. Most Programmers uses the ASC constraint in the ORDER BY clause and it is not necessary to use the ASC constraint with ORDER BY clause if we want to generate the result in ascending order.

First, let’s use the ORDER BY clause before the GROUP BY clause in the MariaDB with the help of the SELECT statement:

SELECT * FROM USA_HP ORDER BY NAME_HP DESC
GROUP BY NAME_HP 
LIMIT 10;

In this query, it will throw a syntax error for using the ORDER BY clause before the GROUP. So, whenever the GROUP BY clause is used it has to be done before with the ORDER BY clause with the SELECT statement.

Here it is important to understand that to get a proper result, we need to use the GROUP BY clause should be used before ORDER BY.

The sample example of the GROUP BY clause with the ORDER BY clause in the USA_HP is given below:

SELECT * FROM usa_hp GROUP BY hp_name 
ORDER BY hp_dollarprice DESC
LIMIT 10;

In this query, the GROUP BY clause uses to group all names of the HP from the HP_NAME column and arrange the hp_dollarprice column in descending order for the resultset.

And bring the top 10 records as the output by using the LIMIT 10 from the USA_HP table by using the SELECT statement.

Read: MariaDB DATEDIFF Function

MariaDB Order By Date

In this section, we will use the ORDER BY clause for the DATE_COLUMN in the MariaDB with the help of the examples.

First, let’s have a look at the USA_POSTMATES table with the help of the SELECT statement as shown below:

SELECT * FROM USA_POSTMATES;

In this query, we have retrieved all records of the USA_POSTMATES table with the help of the SELECT statement in the query.

Now, let’s retrieve all records from the USA_POSTMATES table with help of the ORDER BY clause for the DATE_COLUMN in the ascending or descending order as shown in the below query:

SELECT * FROM USA_POSTMATES ORDER BY ORDER_DATE DESC;

In this query, the ORDER BY clause has retrieved all records from the USA_POSTMATES table and arranged the result set by the ORDER_DATE column in descending order.

Read: MariaDB AUTO_INCREMENT

MariaDB Order By Index

In this section, we will learn how to use the index constraint with the ORDER BY clause in the MariaDB with the help of syntax and examples.

In MariaDB, the INDEX is a performance tuning method that retrieves all records from the table fast. It creates an entry from each value that appeared in the index columns.

So there are two ways to create an index, the first way is to use the CREATE TABLE statement to create a table or we can use the CREATE INDEX statement when the table is created. The syntax of creating an index by using the CREATE TABLE statement is given below:

CREATE TABLE tbl_name
( 
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  column_n datatype [ NULL | NOT NULL ],

  INDEX index_name [ USING BTREE | HASH ]
    (index_col1 [(length)] [DESC | ASC], 
     index_col2 [(length)] [DESC | ASC],

     index_col_n [(length)] [DESC | ASC])
);

OR, to create an index by using the CREATE INDEX statement after the creation of the table which is given below:

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
  [ USING BTREE | HASH ]
  ON tbl_name
    (index_col1 [(length)] [DESC | ASC], 
     index_col2 [(length)] [DESC | ASC],
     index_col_n [(length)] [DESC | ASC]);

The syntax explanation:

• UNIQUE: It indicates the combination of values in the INDEX column must be unique.
• FULLTEXT: It indexes the entire column and dosn’t do prefixing. The INNODB and MyISAM table support this option.
• SPATIAL: It indexs the entire column and doesn’t all index column contain NULL values. In the INNODB ( starting from MariaDB 5.7) and MyISAM supports this option.
• index_name: The Index name given as the name of the table.
• tbl_name: The tbl_name for which we have to create index.
• index_col1,index_col2,index_coln: the column which will used for the index.
• length: [optional] If specified only prefix of the column is indexed not the entire column.
• ASC: [optional] The index is sorted in the ascending order in that column.
• DESC: [optional] The index is sorted in the descending order for that column.

The sample example for the use of the INDEX after the creation of the USA_HP table then creating an index with the help of the CREATE INDEX statement which is given below:

CREATE TABLE USA_HP(
HP_ID INT AUTO_INCREMENT PRIMARY KEY,
HP_NAME VARCHAR(100),
HP_DOLLARPRICE FLOAT,
HP_ORIGINALPRICE FLOAT);

CREATE INDEX USA_HPINDEX ON USA_HP (HP_NAME);

In this query, the CREATE TABLE statement already created a USA_HP table. The CREATE INDEX statement would create indexes called USA_HPINDEX that consist of the HP_NAME field.

First, let’s have a look at the ORDER BY clause before the EXPLAIN statement by the SELECT statement in the USA_HP table:

SELECT * FROM USA_HP ORDER BY NAME_HP DESC;

In the above query, the MariaDB ORDER BY clause would return all records field of the USA_HP table and the result set as NAME_HP column has been sorted in descending order.

The sample example of the INDEX with the ORDER BY clause is given below:

EXPLAIN SELECT * FROM usa_hp ORDER BY hp_name DESC;

In this method with the help of EXPLAIN statement, we get all index numbers for all the HP_NAME columns from the USA_HP table by using the SELECT statement.

In this image, it says “EXTRA: USING FILESORT” which means that MariaDB can’t use the index for sorting in the result set so it must sort the rows by itself. It can be done in memory or on the disk, but it always calls this process a filesort (taken from High-Performance MySQL).

If we want to sort the whole table, then the index should not be used. Mysql should choose wisely to retrieve the table and sort it.

You may also like to read the following MariaDB tutorials.

• MariaDB Create Sequence
• MariaDB If Null + Examples
• MariaDB Delete Row
• MariaDB Full Outer Join
• MariaDB Primary Key
• MariaDB Transaction
• MariaDB Rename Column

In this MariaDB tutorial, we have studied the use of the MariaDB Order By clause and we have also covered some examples. There are lists of the topic that comes under discussion:

• MariaDB order by
• MariaDB order by multiple columns
• MariaDB order by desc
• MariaDB order by field
• MariaDB order by null last
• MariaDB order by random
• MariaDB order by limit
• MariaDB order by timestamp
• MariaDB order by before group by
• MariaDB order by date
• MariaDB order by index