Python MySQL - Order By

---

While fetching data using SELECT query, you can sort the results in desired order (ascending or descending) using the OrderBy clause. By default, this clause sorts results in ascending order, if you need to arrange them in descending order you need to use “DESC” explicitly.

Syntax

Following is the syntax SELECT column-list

FROM table_name
[WHERE condition]
[ORDER BY column1, column2,.. columnN] [ASC | DESC]; of the ORDER BY clause:

Example

Assume we have created a table in MySQL with name EMPLOYEES as −

mysql> CREATE TABLE EMPLOYEE(
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT
);
Query OK, 0 rows affected (0.36 sec)

And if we have inserted 4 records in to it using INSERT statements as −

mysql> INSERT INTO EMPLOYEE VALUES
   ('Krishna', 'Sharma', 19, 'M', 2000),
   ('Raj', 'Kandukuri', 20, 'M', 7000),
   ('Ramya', 'Ramapriya', 25, 'F', 5000),
   ('Mac', 'Mohan', 26, 'M', 2000);

Following statement retrieves the contents of the EMPLOYEE table in ascending order of the age.

mysql> SELECT * FROM EMPLOYEE ORDER BY AGE;
+------------+-----------+------+------+--------+
| FIRST_NAME | LAST_NAME | AGE  | SEX  | INCOME |
+------------+-----------+------+------+--------+
| Krishna    | Sharma    |   19 |    M |   2000 |
| Raj        | Kandukuri |   20 |    M |   7000 |
| Ramya      | Ramapriya |   25 |    F |   5000 |
| Mac        | Mohan     |   26 |    M |   2000 |
+------------+-----------+------+------+--------+
4 rows in set (0.04 sec)

You can also retrieve data in descending order using DESC as −

mysql> SELECT * FROM EMPLOYEE ORDER BY FIRST_NAME, INCOME DESC;
+------------+-----------+------+------+--------+
| FIRST_NAME | LAST_NAME | AGE  | SEX  | INCOME |
+------------+-----------+------+------+--------+
| Krishna    | Sharma    |   19 |    M |   2000 |
| Mac        | Mohan     |   26 |    M |   2000 |
| Raj        | Kandukuri |   20 |    M |   7000 |
| Ramya      | Ramapriya |   25 |    F |   5000 |
+------------+-----------+------+------+--------+
4 rows in set (0.00 sec)

ORDER BY clause using python

To retrieve contents of a table in specific order, invoke the execute() method on the cursor object and, pass the SELECT statement along with ORDER BY clause, as a parameter to it.

Example

In the following example we are creating a table with name and Employee, populating it, and retrieving its records back in the (ascending) order of their age, using the ORDER BY clause.

import mysql.connector

#establishing the connection
conn = mysql.connector.connect(
   user='root', password='password', host='127.0.0.1', database='mydb')

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Doping EMPLOYEE table if already exists.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
sql = '''CREATE TABLE EMPLOYEE(
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT
)'''
cursor.execute(sql)

#Populating the table
insert_stmt = "INSERT INTO EMPLOYEE (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) 
   VALUES (%s, %s, %s, %s, %s)"

data = [('Krishna', 'Sharma', 26, 'M', 2000), 
   ('Raj', 'Kandukuri', 20, 'M', 7000),
   ('Ramya', 'Ramapriya', 29, 'F', 5000),
   ('Mac', 'Mohan', 26, 'M', 2000)]
cursor.executemany(insert_stmt, data)
conn.commit()

#Retrieving specific records using the ORDER BY clause
cursor.execute("SELECT * from EMPLOYEE ORDER BY AGE")
print(cursor.fetchall())

#Closing the connection
conn.close()

Output

[('Raj', 'Kandukuri', 20, 'M', 7000.0), 
   ('Krishna', 'Sharma', 26, 'M', 2000.0), 
   ('Mac', 'Mohan', 26, 'M', 2000.0), 
   ('Ramya', 'Ramapriya', 29, 'F', 5000.0)
]

In the same way you can retrieve data from a table in descending order using the ORDER BY clause.

Example

import mysql.connector

#establishing the connection
conn = mysql.connector.connect(
   user='root', password='password', host='127.0.0.1', database='mydb')

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Retrieving specific records using the ORDERBY clause
cursor.execute("SELECT * from EMPLOYEE ORDER BY INCOME DESC")
print(cursor.fetchall())

#Closing the connection
conn.close()

Output

[('Raj', 'Kandukuri', 20, 'M', 7000.0), 
   ('Ramya', 'Ramapriya', 29, 'F', 5000.0), 
   ('Krishna', 'Sharma', 26, 'M', 2000.0), 
   ('Mac', 'Mohan', 26, 'M', 2000.0)
]