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