Python MySQL - Order By Clause

MySQL Connector library helps establish a connection between Python and MySQL, enabling you to interact with the database. One of the most common SQL operations is sorting the result set using the ORDER BY clause, which allows you to arrange the rows in either ascending or descending order. This operation is useful when you want to retrieve sorted data from your database.

By default, it arranges the results in ascending order (ASC). If you want the results in descending order, you can explicitly use DESC.

Syntax

SELECT column1, column2
FROM table_name
ORDER BY column_name ASC|DESC;

• ASC: Sorts in ascending order (default).
• DESC: Sorts in descending order.

The following programs will help you understand this better.

DATABASE IN USE:

Examples to Understand the ORDER BY Clause

Example 1: Arrange Data in Ascending Order by Name

This example demonstrates how to use the ORDER BY clause to arrange student names in ascending order.

import mysql.connector

# Connecting to the Database
mydb = mysql.connector.connect(
  host='localhost',
  database='College',
  user='root',
  password=''
)

cs = mydb.cursor()

# SQL statement to order by Name in ascending order
statement = "SELECT * FROM Student ORDER BY Name"
cs.execute(statement)

# Fetching and printing the result set
result_set = cs.fetchall()
for x in result_set:
    print(x)

# Disconnecting from the database
mydb.close()

Output:

Explanation: The SQL query fetches all records from the Student table and sorts them by the Name column in ascending order (default behavior).

Example 2: Arrange Data in Descending Order by Name

This example demonstrates how to use the ORDER BY clause to arrange student names in descending order.

import mysql.connector

# Connecting to the Database
mydb = mysql.connector.connect(
  host='localhost',
  database='College',
  user='root',
)

cs = mydb.cursor()

# SQL statement to order by Name in descending order
statement = "SELECT * FROM Student ORDER BY Name DESC"
cs.execute(statement)

# Fetching and printing the result set
result_set = cs.fetchall()
for x in result_set:
    print(x)

# Disconnecting from the database
mydb.close()

Output:

Explanation: "DESC" keyword is used to sort the Name column in descending order.

Example 3: Get Names Arranged by Roll Number in Descending Order

This example demonstrates how to retrieve student names sorted by their roll numbers in descending order.

import mysql.connector

# Connecting to the Database
mydb = mysql.connector.connect(
  host='localhost',
  database='College',
  user='root',
)

cs = mydb.cursor()

# SQL statement to order by Roll_no in descending order
statement = "SELECT Name FROM Student ORDER BY Roll_no DESC"
cs.execute(statement)

# Fetching and printing the result set
result_set = cs.fetchall()
for x in result_set:
    print(x)

# Disconnecting from the database
mydb.close()

Output:

Explanation: This query sorts the Student table by the Roll_no column in descending order and retrieves the Name of the students.