MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

ORDER BY CLAUSE
MySQL ORDER BY Clause
The MySQL ORDER BY clause is used to sort one or more columns of a table in provided order that can be either ascending or descending order. By default, it sorts the column(s) in ascending order if the sort order is not specified.
The sort is specified with two keywords; ASC for ascending order and DESC for descending order.
Using the ORDER BY clause, we can sort multiple columns of a table and provide different sort orders for each column. For instance, we can sort the result set first by one column, and then by another column to the first column, and so on.
Syntax
Following is the syntax of ORDER BY clause in MySQL 
SELECT column-list
FROM table_name
[ORDER BY column1, column2, ..., columnN] [ASC|DESC]
Here,
column-list are the names of the columns that we want to retrieve from the table_name.
column1, column2,...columnN are the column(s) that we want to order (sort).
ASC will sort the columns in ascending order.
DESC will sort the columns in descending order.
By default, the ORDER BY clause sorts the provided column in Ascending order.
Example
Firstly, let us create a table named CUSTOMERS using the following query 
CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);
The following query inserts 7 records into the above-created table 
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );
Execute the following query to verify whether the CUSTOMERS table is created or not 
Select * from CUSTOMERS;
The CUSTOMERS table has been created successfully 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
Now, let us display all the columns from the CUSTOMERS table, sorted by the NAME column 
By default, the ORDER BY clause sorts the provided column in Ascending order.
SELECT * FROM CUSTOMERS
ORDER BY NAME;
Output
As we can see in the output below, the NAME column is sorted in Ascending order.
ID
NAME
AGE
ADDRESS
SALARY
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
3
Kaushik
23
Kota
2000.00
2
Khilan
25
Delhi
1500.00
6
Komal
22
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
1
Ramesh
32
Ahmedabad
2000.00
ORDER BY with DESC
We can sort a particular column of a table in descending order by using the ORDER BY clause along with the DESC keyword. Let us understand with the following example.
Example
In the following query, we are displaying all the columns from the CUSTOMERS table, sorted by the NAME column in descending order 
SELECT * FROM CUSTOMERS
ORDER BY NAME DESC;
Output
As we can see in the output below, the NAME column is sorted in descending order.
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
7
Muffy
24
Indore
10000.00
6
Komal
22
Hyderabad
4500.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
5
Hardik
27
Bhopal
8500.00
4
Chaitali
25
Mumbai
6500.00
ORDER BY with Multiple Columns
We can also sort multiple columns of a MySQL table. To do so, we need to specify all the column names in the ORDER BY clause.
Example
Here, we are selecting all the columns from the CUSTOMERS table, sorted by the ADDRESS and NAME columns.
SELECT * FROM CUSTOMERS
ORDER BY ADDRESS, NAME;
Output
The above query first sorts the ADDRESS column in ascending order, and for any rows that have the same ADDRESS value, they will be sorted by the NAME column in ascending order.
This means, all the rows with the same ADDRESS value will be grouped together and sorted by NAME.
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
5
Hardik
27
Bhopal
8500.00
2
Khilan
25
Delhi
1500.00
6
Komal
22
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
ORDER BY with ASC and DESC
In MySQL, we can order the columns with ASC and DESC in the same query. The column provided first with ASC will be sorted in Ascending order and the column provided second with DESC will be sorted in descending order.
Example
In this query, we are selecting all the columns from the CUSTOMERS table, sorted ascending by the AGE and descending by the SALARY column 
SELECT * FROM CUSTOMERS
ORDER BY AGE ASC, SALARY DESC;
Output
On executing the given program, the output is displayed as follows 
ID
NAME
AGE
ADDRESS
SALARY
6
Komal
22
Hyderabad
4500.00
3
Kaushik
23
Kota
2000.00
7
Muffy
24
Indore
10000.00
4
Chaitali
25
Mumbai
6500.00
2
Khilan
25
Delhi
1500.00
5
Hardik
27
Bhopal
8500.00
1
Ramesh
32
Ahmedabad
2000.00
ORDER BY with LENGTH()
We can use the LENGTH() function with the ORDER BY clause in MySQL to sort the values present in a particular column based on the length.
Example
Using the following query, we are sorting the ADDRESS column based on the length 
SELECT * FROM CUSTOMERS
ORDER BY LENGTH(ADDRESS) ASC;
Output
The output for the program above is produced as given below 
ID
NAME
AGE
ADDRESS
SALARY
3
Kaushik
23
Kota
2000.00
2
Khilan
25
Delhi
1500.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
7
Muffy
24
Indore
10000.00
1
Ramesh
32
Ahmedabad
2000.00
6
Komal
22
Hyderabad
4500.00
Order By Clause Using a Client Program
Besides using MySQL ORDER BY clause to sort one or more columns of a table, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.
Syntax
Following are the syntaxes of this operation in various programming languages 
PHPNodeJSJavaPython
To sort one or more columns of a MySQL table through PHP program, we need to execute SELECT statement with ORDER BY clause using the mysqli function query() as follows 
$sql = "SELECT COLUMN1, COLUMN2, .. FROM TABLE_NAME 
ORDER BY COLUMN1, COLUMN2, ... ASC|DESC";
$mysqli->query($sql);
Example
Following are the programs 
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s", $mysqli->connect_error);
   exit();
}
//printf('Connected successfully.');
$sql = 'SELECT * from tutorials_tbl ORDER BY tutorial_author ASC';
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   printf("Table records based on 'tutorial_author' in ascending order: \n");
   while($row = $result->fetch_assoc()) {
      printf("ID %d, Title: %s, Author: %s ",  
         $row["tutorial_id"],
         $row["tutorial_title"],
         $row["tutorial_author"]);    
      printf("\n");         
   }
} else {
   printf('No record found.');
}
mysqli_free_result($result);
$mysqli->close();
Output
The output obtained is as follows 
Table records based on 'tutorial_author' in ascending order:
ID 5, Title: Learn MySQL, Author: Abdul S
ID 4, Title: Learn PHP, Author: John Poul
ID 2, Title: PHP Tut, Author: New Author
ID 1, Title: Java Tutorial, Author: new_author
ID 3, Title: JAVA Tutorial, Author: Sanjay          
﻿
﻿
﻿
﻿
﻿
﻿
﻿
﻿
﻿
﻿

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

ORDER BY CLAUSE

MySQL ORDER BY Clause

Syntax

Example

Output

ORDER BY with DESC

Example

Output

ORDER BY with Multiple Columns

Example

Output

ORDER BY with ASC and DESC

Example

Output

ORDER BY with LENGTH()

Example

Output

Order By Clause Using a Client Program

Syntax

Example

Output