Order Result of SELECT Query in MySQL

It is common to select certain data or rows from a table. The rows are returned in the order in which they appear in the table. We may sometimes require that the rows we select from the table must be returned to us in ascending or descending order with respect to some column.

The “ORDER BY” statement is used to order the results with respect to some column. The following example will provide more clarity.

Suppose, we have a table which consists of various fields including the “name” field. We want to select all the rows from the table but we want the rows must be ordered in alphabetic order of the names. Here is where the “ORDER BY” statement comes into play. This scenario requires us to order the results in ascending order of the “name” field.

The “ORDER BY” statement ,by default, orders the specified column in ascending order. If you want the result to be ordered in descending order, you need to specify the same. To oder the result in descending order, the keyword “DESC” is to be specified.

Syntax

Ascending order

SELECT * FROM table_name ORDER BY column_name

Descending order

SELECT * FROM table_name ORDER BY column_name DESC

Steps invloved to order the data retrieved from a table using MySQL in python

• import MySQL connector

• establish connection with the connector using connect()

• create the cursor object using cursor() method

• create a query using the appropriate mysql statements

• execute the SQL query using execute() method

• close the connection

Suppose we have a table named “Student” as follows −

+----------+---------+-----------+------------+
|    Name  |   Class |    City   | Marks      |
+----------+---------+-----------+------------+
|    Karan |    4    |  Amritsar |    95      |
|    Sahil |    6    |  Amritsar |    93      |
|    Kriti |    3    |    Batala |    88      |
|   Khushi |    9    |    Delhi  |    90      |
|    Kirat |    5    |    Delhi  |    85      |
+----------+---------+-----------+------------+

Example

We want to select all the rows from the table but in alphabetic order of their names. In short, we want to order the result in ascending order of names.

import mysql.connector

db=mysql.connector.connect(host="your host", user="your username", password="your
password",database="database_name")

cursor=db.cursor()

query= "SELECT * FROM Students ORDER BY Name"
cursor.execute(query)
for row in cursor:
   print(row)

The above code when executed succesfully returns the rows in ascending or alphabetic order of the names of the students.

Output

(‘Amit’ , 9 , ‘Delhi’ , 90)
(‘Karan’, 4 ,’Amritsar’ , 95)
(‘Kriti’ , 3 , ‘Batala’ ,88)
(‘Priya’ , 5 , ‘Delhi’ ,85)
(‘Sahil’ , 6 , ‘Amritsar’ ,93)

All the rows displayed are in alphabetic order of the names. Similarly, the rows could have been arranged in ascending or descending order of the marks following the similar syntax.