Khóa học miễn phí MySQL – Order By Clause nhận dự án làm có lương

MySQL – ORDER BY CLAUSE

Table of content


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 −

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);

To sort one or more columns of a MySQL table through Node.js program, we need to execute SELECT statement with ORDER BY clause using the query() function of the mysql2 library as follows −

sql= SELECT column-list FROM table_name
[ORDER BY column1, column2, ..., columnN] [ASC|DESC]  
con.query(sql);

To sort one or more columns of a MySQL table through Java program, we need to execute SELECT statement with ORDER BY clause using the JDBC function executeUpdate() as follows −

String sql = "SELECT column-list FROM table_name 
[ORDER BY column1, column2, ..., columnN] [ASC|DESC]";
statement.executeQuery(sql);

To sort one or more columns of a MySQL table through Python program, we need to execute SELECT statement with ORDER BY clause using the execute() function of the MySQL Connector/Python as follows −

order_by_clause_query = SELECT column-list FROM table_name 
[ORDER BY column1, column2, ..., columnN] [ASC|DESC]
cursorObj.execute(order_by_clause_query);

Example

Following are the programs −

$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          
var mysql = require(''mysql2'');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  //Creating a Database
  sql = "create database TUTORIALS"
  con.query(sql);

  //Select database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Creating table
  sql = "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));"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(1,''Ramesh'', 32, ''Hyderabad'', 2000.00),(2,''Khilan'', 25, ''Delhi'', 1500.00),(3,''kaushik'', 23, ''Hyderabad'', 2000.00),(4,''Chaital'', 25, ''Mumbai'', 6500.00),(5,''Hardik'', 27, ''Vishakapatnam'', 8500.00),(6, ''Komal'',22, ''Vishakapatnam'', 4500.00),(7, ''Muffy'',24, ''Indore'', 10000.00);"
  con.query(sql);

  //Using ORDER BY Clause
  sql = "SELECT * FROM CUSTOMERS ORDER BY NAME;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});               

Output

The output produced is as follows −

Connected!
--------------------------
[
  {
    ID: 4,
    NAME: ''Chaital'',
    AGE: 25,
    ADDRESS: ''Mumbai'',
    SALARY: ''6500.00''
  },
  {
    ID: 5,
    NAME: ''Hardik'',
    AGE: 27,
    ADDRESS: ''Vishakapatnam'',
    SALARY: ''8500.00''
  },
  {
    ID: 3,
    NAME: ''kaushik'',
    AGE: 23,
    ADDRESS: ''Hyderabad'',
    SALARY: ''2000.00''
  },
  {
    ID: 2,
    NAME: ''Khilan'',
    AGE: 25,
    ADDRESS: ''Delhi'',
    SALARY: ''1500.00''
  },
  {
    ID: 6,
    NAME: ''Komal'',
    AGE: 22,
    ADDRESS: ''Vishakapatnam'',
    SALARY: ''4500.00''
  },
  {
    ID: 7,
    NAME: ''Muffy'',
    AGE: 24,
    ADDRESS: ''Indore'',
    SALARY: ''10000.00''
  },
  {
    ID: 1,
    NAME: ''Ramesh'',
    AGE: 32,
    ADDRESS: ''Hyderabad'',
    SALARY: ''2000.00''
  }
]         
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class OrderByClause {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT * FROM CUSTOMERS ORDER BY NAME DESC";
            rs = st.executeQuery(sql);
            System.out.println("Table records(in DESC order): ");
            while(rs.next()){
              String id = rs.getString("Id");
              String name = rs.getString("Name");
              String age = rs.getString("Age");
              String address = rs.getString("Address");
              String salary = rs.getString("Salary");
              System.out.println("Id: " + id +", Name: " + name + ", Age: " + age + ", Address: " + address + ", Salary: " + salary);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}                             

Output

The output obtained is as shown below −

Table records(in DESC order): 
Id: 1, Name: Ramesh, Age: 32, Address: Ahmedabad, Salary: 2000.00
Id: 7, Name: Muffy, Age: 24, Address: Indore, Salary: 10000.00
Id: 6, Name: Komal, Age: 22, Address: MP, Salary: 4500.00
Id: 2, Name: Khilan, Age: 25, Address: Delhi, Salary: 1500.00
Id: 3, Name: kaushik, Age: 23, Address: Kota, Salary: 2000.00
Id: 5, Name: Hardik, Age: 27, Address: Bhopal, Salary: 8500.00
Id: 4, Name: Chaitali, Age: 25, Address: Mumbai, Salary: 6500.00         
import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object 
cursorObj = connection.cursor()
order_by_clause_query = """
SELECT *
FROM customers
ORDER BY AGE ASC
"""
cursorObj.execute(order_by_clause_query)
# Fetch all the ordered rows
ordered_rows = cursorObj.fetchall()
# Printing the ordered rows
for row in ordered_rows:
    print(row)
cursorObj.close()
connection.close()                                         

Output

Following is the output of the above code −

(3, ''kaushik'', 23, ''Hyderabad'', Decimal(''11000.00''))
(7, ''Muffy'', 24, ''Delhi'', Decimal(''10000.00''))
(2, ''Khilan'', 25, ''Kerala'', Decimal(''8000.00''))
(4, ''Chaital'', 25, ''Mumbai'', Decimal(''1200.00''))
(5, ''Hardik'', 27, ''Vishakapatnam'', Decimal(''10000.00''))
(6, ''Komal'', 29, ''Vishakapatnam'', Decimal(''7000.00''))
(1, ''Ramesh'', 32, ''Hyderabad'', Decimal(''4000.00''))  

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc