MariaDB Order By Multiple Columns

Having rows organized a certain way is frequently helpful when analyzing data. In this tutorial, We’ll use a variety of examples to demonstrate how MariaDB ORDER BY arranges data in either ascending or descending order according to one or more columns.

The output of a MariaDB query often returns rows in any order. The ORDER BY keyword must be used if you wish to sort the output in a certain order. According to one or more columns given in the ORDER BY clause, the rows are sorted. Data is sorted by ORDER BY in ascending order unless you specify differently.

This tutorial will concentrate on MariaDB’s row-ordering functionality. The complete list of topics, we’ll cover in this MariaDB tutorial is provided below.

• How to Order By Multiple Columns in MariaDB
• How to Order By 2 Columns in MariaDB
• How to Order By Multiple Columns By Date in MariaDB
• How to Order By Multiple Columns Based on Condition in MariaDB
• How to Order By Multiple Columns By Group in MariaDB
• MariaDB Order By Multiple Columns Concat
• MariaDB Order By Multiple Columns Descending Order
• How to use the DISTINCT while Order BY Multiple Columns in MariaDB
• MariaDB Order By Multiple Columns From Select
• MariaDB Order By Multiple Columns Greater Than
• MariaDB Order By Multiple Columns Having Same Name
• MariaDB Order By Multiple Columns In
• MariaDB Order By Multiple Columns Join
• MariaDB Order By Multiple Columns Limit
• How to Order By Multiple Columns with Like in MariaDB
• MariaDB Order By Multiple Columns Rank
• MariaDB Order By Multiple Columns Update
• MariaDB Order By Multiple Columns Year

MariaDB Order By Multiple Columns

When you just retrieve the data from the MariaDB table, it is not presented in any specific order. ORDER BY clause is used to show data in a meaningful manner. ORDER BY organizes your results according to the column name supplied in the SELECT query’s clause.

• It enables us to set orders in either one or more columns.
• It enables us to specify whether to format sort results as ASC or DESC.

The syntax is given below.

SELECT * FROM [table_name] 
ORDER BY 
[column_name1 ] [ASC|DESC] ,  
[column_name2] [ASC|DESC],..

Where,

• table_name: It is the table whose column will be sorted or presented in a specific order.
• ORDER BY: The result set can be sorted ascending or descending using the ORDER BY keyword.
• colomn_name1, column_name2: The column names that you want to be in a specific order.
• ASC|DESC: The rows in the result set can then be sorted using ASC or DESC in either ascending order (ASC) or descending order (DESC). ASC is used by default in the ORDER BY clause.

Let’s take an example and understand how to order the columns using the ORDER BY keyword.

For the example here we will use the database classiccars that contains information about the cars bought or ordered by different customers from different places, and we will fetch all the customers who belong to the USA.

Use the below query to get all the customers who belong to the USA.

SELECT customerName,city,country FROM customers
WHERE country = 'USA';

In the above output, you can see the name of the customers in a random order in the column customerName, let’s use the below query to order the customer name using the ORDER BY keyword.

SELECT customerName,city,country FROM customers
WHERE country = 'USA'
ORDER BY customerName;

As you can again see in the output, the column customerName contains all the names in a specific order like A to Z because of the ORDER BY keyword. But the column city also doesn’t have any order. So here we need to order the multiple columns such as customerName and city in a single query.

For that use commas to separate the names of your various columns in the ORDER BY clause (,). As shown in the below query.

SELECT customerName,city,country FROM customers
WHERE country = 'USA'
ORDER BY city,customerName;

The SELECT query’s Order by clause is used to specify the order in which results should appear. When defining several columns, you only need to use the comma (,) to separate the column names.

Read: MariaDB Order By Decreasing

MariaDB Order By Multiple Columns By Date

We have already learned how to order the multiple columns from the above subsection, in this section, we will order the column that contains the data as a date. To order the date type column follow the below steps:

The database classiccars contains several tables, one of the table names is orders that contain the classic cars order of each customer. Let’s see the order date of each customer using the below query.

SELECT customerName,orderDate FROM customers
JOIN orders ON customers.customerNumber = orders.customerNumber;

The above query shows the order date of each customer with the customer name in the column customerName and orderDate. As we can see both columns exist in two different tables so we joined the two tables using the JOIN clause. If you want to know about the JOIN clause then refer to our tutorial MariaDB Join with Examples

Look at the above output the column orderDate which of type date doesn’t have any order, to make it in order, use the below query.

SELECT customerName,orderDate FROM customers
JOIN orders ON customers.customerNumber = orders.customerNumber
ORDER BY orderDate;

Now the column orderDate is sorted in ascending order as you can see. So this is how to order the column of type date using the ORDER BY clause.

Read: MariaDB Update Statement 

MariaDB Order By Multiple Columns Descending Order

The data of the columns can be arranged in descending order using the keyword DESC that we have learned in the above subsection “MariaDB Order By Multiple Columns”. By default, the ORDER BY clause arranges the data of the column in ascending order.

The syntax is given below.

SELECT * FROM [table_name] 
ORDER BY 
[column_name1 ] [ASC|DESC] ,  
[column_name2] [ASC|DESC],..

Where,

• table_name: It is the table whose column will be sorted or presented in a specific order.
• ORDER BY: The result set can be sorted ascending or descending using the ORDER BY keyword.
• colomn_name1, column_name2: The column names that you want to be in a specific order.
• ASC|DESC: The rows in the result set can then be sorted using ASC or DESC in either ascending order (ASC) or descending order (DESC). ASC is used by default in the ORDER BY clause.

Let’s take the example and arrange the column data in descending order by following the below steps:

Use the below query to check the city and country of the customers of the classiccars database.

SELECT customerName,city,country FROM customers;

Now look at the column country that contains the name of the countries in random order, and use the below query to arrange the name of the countries in descending order.

SELECT customerName,city,country FROM customers
ORDER BY country DESC;

After the keyword ORDER BY, we have specified the column name with the keyword DESC to arrange the column country data in descending order. A column contains the string data so it will be arranged in alphabetical order from Z to A.

Now arrange the column data with multiple columns using the below query.

SELECT customerName,city,country FROM customers
ORDER BY country DESC, customerName, city;

Look at the column country again most of the users are from the USA, this is how to arrange the data of the specific column in descending order using the keyword DESC with the ORDER BY clause.

Read: MariaDB Not Between

MariaDB Order By Multiple Columns By Group

One of the best MariaDB clauses is GROUP BY. It creates summary rows from rows that share the same values. For each group, the clause returns a row.

MariaDB GROUP BY appears in a query after the FROM and WHERE clauses but before the HAVING DISTINCT, ORDER BY, and LIMIT clauses. In this section, you will see how to order the multiple columns with GROUP BY.

The syntax is given below.

SELECT * FROM [table_name] 
GROUP BY [column_name,...]
ORDER BY 
[column_name1 ] [ASC|DESC] ,  
[column_name2] [ASC|DESC],..

Let’s take an example, here we will use another table payments of database classiccars which has several information related to payments like amount, credit limit, payment date and etc.

Now you want to know the customer’s name, and the credit limit of the customer with the amount and date, for that use the below query.

SELECT customerName,country,creditLimit,paymentDate,amount FROM customers
JOIN payments ON customers.customerNumber = payments.customerNumber

The above query shows the information from the two tables the customers and payments, the columns customerName, country from the customers table, and creditLimit, paymentDate, and amount from the payments table. For the columns from both tables, the JOIN clause is used.

From the above output, we know the customer name, credit limit, payment date, and amount, now group the customer according to customer name using the below query.

SELECT customerName,country,creditLimit,paymentDate,amount FROM customers
JOIN payments ON customers.customerNumber = payments.customerNumber
GROUP BY customerName

Now use the below query to order the customers according to the country and payment date.

SELECT customerName,country,creditLimit,paymentDate,amount FROM customers
JOIN payments ON customers.customerNumber = payments.customerNumber
GROUP BY customerName
ORDER BY country,paymentDate;

We trust you now understand how to utilize the MariaDB GROUP BY clause in conjunction with the ORDER BY clause on the table columns. To help you understand, we provided a detailed explanation and utilized an example.

Read: Working with MariaDB Cast

MariaDB Order By Multiple Columns Based on Condition

The MariaDB WHERE Clause is used by the structured query language (SQL) to filter data according to the applied conditions. It frequently appears in statements that select, update, or delete. In this section, we will filter the customer according to certain conditions and then order the result using the ORDER BY keyword to make the result meaningful.

Use the same table that we have used in the above subsections for example and filter the customers whose credit limit is greater than 80000 and who live in the USA.

Execute the below query to know the customers who have a credit limit of 80000 and the residents of the USA.

SELECT customerName,country,creditLimit,paymentDate, COUNT(customerName) AS numberof_Customer FROM customers
JOIN payments ON customers.customerNumber = payments.customerNumber
WHERE creditLimit >= 80000 AND country = "USA"
Group by customerName;

The above output shows the number of customers who have a credit limit of more than 80000 and live in the USA. Now use the below query to order the customer according to the payment date and number of customers.

SELECT customerName,country,creditLimit,paymentDate, COUNT(customerName) AS numberof_Customer FROM customers
JOIN payments ON customers.customerNumber = payments.customerNumber
WHERE creditLimit >= 80000 AND country = "USA"
Group by customerName
ORDER BY paymentDate, numberof_Customer;

We hope that by applying the MariaDB WHERE condition in conjunction with the ORDER BY clause for the multiple columns of the table, you have a better understanding of the subtopic “MariaDB Order By Multiple Columns Based on Condition”. We have defined it thoroughly and used a sample example to provide a better explanation.

Read: MariaDB Substring Function

MariaDB Order By Multiple Columns Distinct

When used with a SELECT statement, MariaDB’s DISTINCT Clause helps to eliminate duplicates from the result. In this section, we will apply the distinct on the column and order the multiple columns.

The syntax is given below.

SELECT DISTINCT column_name,.. FROM [table_name] 
ORDER BY 
[column_name1 ] [ASC|DESC] ,  
[column_name2] [ASC|DESC],..

Where,

• DISTINCT column_name: The DISTINCT keyword used before the columns whose duplicate values need to be removed.
• table_name: It is the table whose column will be sorted or presented in a specific order.
• ORDER BY: The result set can be sorted ascending or descending using the ORDER BY keyword.
• colomn_name1, column_name2: The column names that you want to be in a specific order.
• ASC|DESC: The rows in the result set can then be sorted using ASC or DESC in either ascending order (ASC) or descending order (DESC). ASC is used by default in the ORDER BY clause.

Let’s take an example to know how to remove duplicate values from the columns and order them in a specific or meaningful way.

Use the below query to know the customer’s name, country, amount, and payment date.

SELECT customerName,country,amount,paymentDate FROM customers
JOIN payments ON customers.customerNumber = payments.customerNumber;

From the above output, we can know the customer’s name, amount, payment date, and country. Let’s use the keyword DISTINCT and remove the duplicated customer name or country.

SELECT Distinct customerName, country,amount,paymentDate FROM customers
JOIN payments ON customers.customerNumber = payments.customerNumber;

Now we have filtered the distinct values from the columns as shown in the above output, Let’s order the result by the amount and payment date using the below query.

SELECT Distinct customerName, country,amount,paymentDate FROM customers
JOIN payments ON customers.customerNumber = payments.customerNumber
ORDER BY amount,paymentDate;

We hope that using the MariaDB DISTINCT and ORDER BY clauses on the table helped you understand the subtopic “MariaDB Order By Multiple Columns Distinct”. We have provided a detailed explanation and an example to help make our point.

Read: MariaDB Row_Number Functioin

MariaDB Order By Multiple Columns Greater Than

The GREATER THAN EQUAL TO (>=) is used to compare two values in this section, we will use the greater than an operator with the ORDER BY clause.

Let’s take an example and find the customers whose payment is greater than or equal to 1 lakh using the below query.

SELECT Distinct customerName,amount,paymentDate FROM customers
JOIN payments ON customers.customerNumber = payments.customerNumber
WHERE amount >= 100000;

From the above output, there are five customers whose amount is greater than or equal to one lakh. Now use the below query to order the column amount and payment date.

SELECT Distinct customerName,amount,paymentDate FROM customers
JOIN payments ON customers.customerNumber = payments.customerNumber
WHERE amount >= 100000
ORDER BY amount,paymentDate;

The above output shows the customers whose amount is greater than 100000 and order the result according to the amount and paymentDate. This is how to use the greater than an operator with the ORDER BY clause.

Read: MariaDB Str_To_Date() Function

MariaDB Order By Multiple Columns Limit

To determine how many records to return, use the LIMIT clause. So in this section, we will use the limit clause after the keyword ORDER BY, in other words, first we will order the result and then get the desired number of results as output.

Let’s take an example by following the below steps:

Use the below query to find the customer name, amount, country, and payment date.

SELECT customerName, country,amount,paymentDate FROM customers
JOIN payments ON customers.customerNumber = payments.customerNumber;

In the above output, we can see the customer’s name, country, amount, and payment date. Let’s order the above result according to amount and payment date and also check the top 5 customer’s from least to the most amount.

SELECT customerName, country,amount,paymentDate FROM customers
JOIN payments ON customers.customerNumber = payments.customerNumber
ORDER BY amount, paymentDate
LIMIT 5;

From the above output, we can see the top 5 customers whose has paid less amount. This is how to use the LIMIT clause with the ORDER BY keyword to get a meaningful result.

Read: Create Function in MariaDB

MariaDB Order By Multiple Columns In

You can define several values in a WHERE clause by using the IN operator. In this section, we will use the IN operator with the ORDER BY keyword. So first we will filter some results using the IN operator and then order them in a specific way.

Let’s take an example by following the below steps:

Use the below query to find the customer’s from the USA and UK.

SELECT customerName, country,amount,paymentDate FROM customers
JOIN payments ON customers.customerNumber = payments.customerNumber
WHERE country IN ("USA","UK");

The above output shows the customers from the USA and UK. Now use the below query to order the result according to the payment date and customer’s name.

SELECT customerName, country,amount,paymentDate FROM customers
JOIN payments ON customers.customerNumber = payments.customerNumber
WHERE country IN ("USA","UK")
ORDER BY paymentDate, customerName;

By applying the MariaDB IN condition with the ORDER BY clause on the table, we hope you have comprehended the subtopic “MariaDB Order By Multiple Columns In”. To help you understand, we provided a detailed explanation and used an example.

Read: Introduction to MariaDB Join with Examples

MariaDB Order By Multiple Columns Join

Rows from two or more tables are combined using a JOIN clause when they share a common column. So in this section, we will get rows or columns from more than one table and order them according to a meaningful way.

Let’s take an example by following the below steps:

The database classiccars contains tables products and orderdetails, the table orderdetails contains columns OrderNumber, productCode, quantityOrdered, priceEach, orderLineNumber, and the table products contains columns productCode, productName, productVendor, productDescription, buyPrice and etc.

Both the tables have a common column which is productCode, so use the below query to know the product name, product description, buying price, and quantity ordered by joining both tables.

SELECT productName,productDescription,buyPrice,quantityOrdered FROM products
JOIN orderdetails ON products.productCode = orderdetails.productCode;

From the above output, we can see the desired results or columns from both tables, now we are going to order the result according to the buyPrice and quantityOrdered.

SELECT productName,productDescription,buyPrice,quantityOrdered FROM products
JOIN orderdetails ON products.productCode = orderdetails.productCode
ORDER BY buyPrice, quantityOrdered;

The above output shows a meaningful result because of the ordering of the columns like buyPrice and quantityOrdered. This is how to use the JOIN clause with the ORDER BY keyword.

Read: MariaDB Set Variable

MariaDB Order By Multiple Columns Like

To look for a specific pattern in a column, use the LIKE operator in a WHERE clause. So in this section, we will use the LIKE clause with the ORDER BY clause.

Let’s take an example and find a product name that starts with the letter A using the below query.

SELECT productName,productDescription,buyPrice,quantityOrdered FROM products
JOIN orderdetails ON products.productCode = orderdetails.productCode
WHERE productName LIKE "a%"

The above output shows the name of the products that start with the letter A, now use the below query to order the products according to the buyPrice and quantityOrdered.

SELECT productName,productDescription,buyPrice,quantityOrdered FROM products
JOIN orderdetails ON products.productCode = orderdetails.productCode
WHERE productName LIKE "a%"
ORDER BY buyPrice, quantityOrdered;

As we can see in the above output, all the product names start with A, and also order the price and quantity ordered in ascending order using the ORDER BY keyword. This is how to use the LIKE clause with the ORDER BY clause.

Read: How to create sequence in MariaDB 

MariaDB Order By Multiple Columns Rank

A window function called RANK() counts the number of a given row starting at one and going down the ORDER BY list, with identical values yielding the same result. In this section, we will use the RANK() with the ORDER BY keyword.

Let’s use the below query to rank the products over the product name.

SELECT  RANK() OVER (PARTITION BY  productName ORDER BY buyPrice ) AS RANK, 
productName,productDescription,buyPrice,quantityOrdered FROM  products
JOIN orderdetails ON products.productCode = orderdetails.productCode

Now order the above result according to the buyPrice and quantityOrdered using the below query.

SELECT  RANK() OVER (PARTITION BY  productName ORDER BY buyPrice ) AS RANK, 
productName,productDescription,buyPrice,quantityOrdered FROM  products
JOIN orderdetails ON products.productCode = orderdetails.productCode
ORDER BY quantityOrdered, buyPrice;

We believe you now understand how to use the multiple columns with the MariaDB RANK() function. We used an illustration and gave a thorough demonstration to provide a better illustration.

Read: How to import CSV files in MariaDB

MariaDB Order By 2 Columns

We already know how to order multiple columns using the ORDER BY clause. In this section, we will only order the result using two columns of the table.

The syntax is given below.

SELECT * FROM [table_name] 
ORDER BY 
[column_name1 ] [ASC|DESC] ,  
[column_name2] [ASC|DESC],..

Where,

• table_name: It is the table whose column will be sorted or presented in a specific order.
• ORDER BY: The result set can be sorted ascending or descending using the ORDER BY keyword.
• colomn_name1, column_name2: The column names that you want to be in a specific order.
• ASC|DESC: The rows in the result set can then be sorted using ASC or DESC in either ascending order (ASC) or descending order (DESC). ASC is used by default in the ORDER BY clause.

Let’s take an example by following the below steps:

Use the below query to know the customers from the United Kingdom.

SELECT customerName,city,country FROM customers
WHERE country = 'UK';

Now use the below query to order the above result according to the city and customerName.

SELECT customerName,city,country FROM customers
WHERE country = 'UK'
ORDER BY city,customerName;

From the above output, we can see the result is ordered by the city and customer name, this is how to use the two columns to order the result.

Read: MariaDB Backup Database

MariaDB Order By Multiple Columns From Select

Here in this section, we will take the columns from the other SELECT statement and then order the result according to our needs.

Let’s take an example by following the below step:

Use the below query to know the customers, city, and country.

SELECT customerName, city, country
FROM (SELECT * FROM customers) AS customer
ORDER BY city, country;

The above query is also called the nested query or we can see that one select statement is within another select statement. In the statement “SELECT * FROM customers” extract all the columns from the table customer, then take a specific column like customerName, city, and the country using another select statement.

We hope you now understand how to use the query’s on table columns with the MariaDB SELECT statement and ORDER BY clause. We have provided a detailed explanation and an example to help make our point.

Read: Create MariaDB Temporary Table 

MariaDB Order By Multiple Columns Update

In this section, we’ll examine how to use the ORDER BY clause with the MariaDB UPDATE statement to update the table columns. The query’s existing record for the column name from the table is updated using the UPDATE statement.

The Syntax is given below.

UPDATE TABLE_NAME
SET COLUMN_NAME= NEW_VALUE
WHERE [CONDITIONS]
ORDER BY COLUMN_NAME [ DESC | ASC ], 
COLUMN_NAME_1 [ DESC | ASC ];

Let’s take an example by following the below steps:

UPDATE STATES_OF_USA 
SET STATE_NAME=''
WHERE STATE_ID=54
ORDER BY STATE_NAME ASC,
STATE_POPULATION DESC;

With the WHERE condition, we updated and set a new value for the STATE_NAME column from the STATES_OF_USA table using the UPDATE statement in the aforementioned query. Using the EQUAL TO operator and the STATE_ID column, the WHERE condition searches the STATES_OF_USA table for a value equal to 54.

Finally, by using the ASC and DESC keywords in the query, we have applied the ORDER BY clause to arrange the STATE_NAME column in ascending order and the STATE_POPULATION column in descending order.

The UPDATE statement will update and set a new value for that column in the STATES_OF _USA table if the WHERE condition turns out to be TRUE. However, this occurs when the WHERE condition receives a FALSE value and the UPDATE statement executes properly but fails to update a new value for that column in the STATES_OF_USA table.

MariaDB Order By Multiple Columns Concat

The CONCAT() method combines many strings into one. In this section, we will use the CONCAT() function with the ORDER BY clause.

Let’s take an example by following the below steps:

Use the below query to know the customer’s name, phone, city, and country.

SELECT CONCAT(contactLastName," ",contactFirstName) AS contactName, phone,city,country 
FROM customers;

The above query within the CONCAT(contactLastName,” “,contactFirstName), combines the two columns’ values into one column or returns a single value.

Now use the below query to order the result according to the phone and city.

SELECT CONCAT(contactLastName," ",contactFirstName) AS contactName, phone,city,country 
FROM customers
ORDER BY phone, city;

From the above output, we can see the column contactName which contains the customer’s last and first name concatenated using the CONCAT() function. Then order the rows according to the phone and city. This is how to add the columns and order them according to the specific way.

MariaDB Order By Multiple Columns Year

The column name in the query or the expression is used by the MariaDB YEAR function to retrieve the year part value. In this section, we will show the column with the year and order them according to our needs.

The syntax for the MariaDB YEAR function and ORDER BY clause is as follows.

SELECT YEAR(EXPRESSION | COLUMN_NAME), EXPRESSION
FROM TABLE_NAME 
ORDER BY EXPRESSION [ DESC | ASC ], 
EXPRESSION [ DESC | ASC ];

Let’s take an example by following the below steps:

SELECT YEAR (PATIENT_ADMITDATE), PATIENT_FIRSTNAME, PATIENT_LASTNAME FROM johns_hopkins_hospital
ORDER BY GENDER DESC,
PATIENT_ID ASC;

In the above query, the PATIENT_FIRSTNAME and PATIENT_LASTNAME columns from the JOHNS HOPKINS HOSPITAL table are used to obtain all records. In the YEAR function, the PATIENT_ADMITDATE column of the JOHNS HOPKINS HOSPITAL table will be used to retrieve the year portion value.

Finally, by using the ORDER BY expression [DESC | ASC], we have ordered the data of the GENDER column in descending order and the PATIENT_ID column in ascending order.

By using the MariaDB YEAR function and the ORDER BY clause on the table’s columns, we hope you have a better understanding of the subtopic “MariaDB Order By Multiple Columns Year”. We have provided a deeper explanation and utilized an illustration to provide a better description.

In this MariaDB tutorial, we have learned how to order the rows using the ORDER BY clause, and also done several examples by following the below topics.

• How to Order By Multiple Columns in MariaDB
• How to Order By 2 Columns in MariaDB
• How to Order By Multiple Columns By Date in MariaDB
• How to Order By Multiple Columns Based on Condition in MariaDB
• How to Order By Multiple Columns By Group in MariaDB
• MariaDB Order By Multiple Columns Concat
• MariaDB Order By Multiple Columns Descending Order
• How to use the DISTINCT while Order BY Multiple Columns in MariaDB
• MariaDB Order By Multiple Columns From Select
• MariaDB Order By Multiple Columns Greater Than
• MariaDB Order By Multiple Columns Having Same Name
• MariaDB Order By Multiple Columns In
• MariaDB Order By Multiple Columns Join
• MariaDB Order By Multiple Columns Limit
• How to Order By Multiple Columns with Like in MariaDB
• MariaDB Order By Multiple Columns Rank
• MariaDB Order By Multiple Columns Update
• MariaDB Order By Multiple Columns Year