ORDER BY Statement in MariaDB

In MariaDB, the ORDER BY clause is an important component for organizing the result set of a query in a certain order. Being aware of the complexities of ORDER BY can significantly improve your capacity to get and evaluate data effectively, regardless of whether you're working with one or more columns, ascending or descending sorting, or both. This article will explore the ORDER BY statement in MariaDB, including its syntax, uses, and best practices.

ORDER BY Clause

The results of a statement are sorted using the ORDER BY clause. It offers the choice to sort the data in either ascending (ASC) or descending (DESC) order and describes the sequence in which the data is performed. If the order is not specified, the order is assumed to be ascending.

Syntax:

SELECT field1, filed2, ... FROM table_name [WHERE condition] ORDER BY field1 [ASC | DESC], filed2 [ASC | DESC], ...;

• SELECT Clause: It specifies the columns you want to retrieve from the table.
  
• FROM Clause: It specifies the table from which the data will be retrieved.
  
• WHERE Clause: It filters the rows based on specific conditions.
  
• ORDER BY Clause: Sorts the result set in either descending (DESC) or ascending (ASC) order according to one or more columns.

Let's create a table products and insert some data into this

CREATE TABLE

Query:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    unit_price DECIMAL(10, 2),
    stock_quantity INT
);

INSERT DATA

Query:

INSERT INTO products VALUES
(1, 'Laptop', 'Electronics', 1200.00, 50),
(2, 'Smartphone', 'Electronics', 800.00, 100),
(3, 'Coffee Maker', 'Appliances', 50.00, 30),
(4, 'Backpack', 'Fashion', 40.00, 80),
(5, 'Desk Chair', 'Furniture', 150.00, 20);

Sorting Based on a Single Column in Ascending Order

Query:

SELECT * FROM products ORDER BY product_name ASC;    

This pulls every column from the "products" table and arranges the result set in ascending alphabetical order by product name.

Output:

Sorting Based on a Single Column in Descending Order

Query:

SELECT product_name, unit_price FROM products ORDER BY unit_price DESC;

This extracts the unit prices and product names from the "products" table and arranges them according to the unit price in decreasing order.

Output:

Sorting Based on a Multiple Columns

Query:

SELECT * FROM products ORDER BY category ASC, unit_price DESC;

This arranges the result set in ascending order by category and descending order by unit price.

Output:

Sorting by Column Number

SELECT product_name, category FROM products ORDER BY 1;

This retrieves the product name and category from the products table and sort the first selected column which is product_name.

Output:

Sort and Filter Based on Stock Quantity

Query:

SELECT * FROM products WHERE category = 'Electronics' ORDER BY stock_quantity ;

The result set is sorted ascendingly by stock amount after retrieving every column for products in the "Electronics" category.

Note:

When using the ORDER BY clause, ASC is the default value. 
Therefore, by default, the result will be sorted in ascending order in the ORDER BY clause if nothing is specified after the column name.

Output:

Conclusion

Using the ORDER BY statement in MariaDB is like to possessing superpowers when it comes to organizing and interpreting data from database queries. You may quickly arrange and analyze your data by following the examples provided below. It's like having a tool that allows you to filter information in the way that best suits your requirements. Try out multiple examples to figure out how this feature may help you get the most out of MariaDB until you get an idea of it.