Python SQLite - Order By

While fetching data using SELECT query, you will get the records in the same order in which you have inserted them.

You can sort the results in desired order (ascending or descending) using the Order By clause. By default, this clause sorts results in ascending order, if you need to arrange them in descending order you need to use “DESC” exppcitly.

Syntax

Following is the syntax of the ORDER BY clause in SQLite.

SELECT column-pst
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

Example

Assume we have created a table with name CRICKETERS using the following query −

sqpte> CREATE TABLE CRICKETERS (
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Age int,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255)
);
sqpte>

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

sqpte> insert into CRICKETERS values( Shikhar ,  Dhawan , 33,  Delhi ,  India );
sqpte> insert into CRICKETERS values( Jonathan ,  Trott , 38,  CapeTown ,  SouthAfrica );
sqpte> insert into CRICKETERS values( Kumara ,  Sangakkara , 41,  Matale ,  Srilanka );
sqpte> insert into CRICKETERS values( Virat ,  Kohp , 30,  Delhi ,  India );
sqpte> insert into CRICKETERS values( Rohit ,  Sharma , 32,  Nagpur ,  India );
sqpte>

Following SELECT statement retrieves the rows of the CRICKETERS table in the ascending order of their age −

sqpte> SELECT * FROM CRICKETERS ORDER BY AGE;
First_Name Last_Name  Age  Place_Of_B Country
---------- ---------- ---- ---------- -------------
Virat      Kohp      30   Delhi      India
Rohit      Sharma     32   Nagpur     India
Shikhar    Dhawan     33   Delhi      India
Jonathan   Trott      38   CapeTown   SouthAfrica
Kumara     Sangakkara 41   Matale     Srilanka
sqpte>

You can use more than one column to sort the records of a table. Following SELECT statements sorts the records of the CRICKETERS table based on the columns AGE and FIRST_NAME.

sqpte> SELECT * FROM CRICKETERS ORDER BY AGE, FIRST_NAME;
First_Name Last_Name  Age  Place_Of_B Country
---------- ---------- ---- ---------- -------------
Virat      Kohp      30   Delhi      India
Rohit      Sharma     32   Nagpur     India
Shikhar    Dhawan     33   Delhi      India
Jonathan   Trott      38   CapeTown   SouthAfrica
Kumara     Sangakkara 41   Matale     Srilanka
sqpte>

By default, the ORDER BY clause sorts the records of a table in ascending order you can arrange the results in descending order using DESC as −

sqpte> SELECT * FROM CRICKETERS ORDER BY AGE DESC;
First_Name Last_Name  Age  Place_Of_B Country
---------- ---------- ---- ---------- -------------
Kumara     Sangakkara 41   Matale     Srilanka
Jonathan   Trott      38   CapeTown   SouthAfrica
Shikhar    Dhawan     33   Delhi      India
Rohit      Sharma     32   Nagpur     India
Virat      Kohp      30   Delhi      India
sqpte>

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 psycopg2

#estabpshing the connection
conn = psycopg2.connect(
   database="mydb", user= postgres , password= password , host= 127.0.0.1 , port=  5432 
)
#Setting auto commit false
conn.autocommit = True

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

#Doping EMPLOYEE table if already exists.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

#Creating a table
sql =    CREATE TABLE EMPLOYEE(
FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT, SEX CHAR(1),
   INCOME INT,
   CONTACT INT
)   
cursor.execute(sql)

#Populating the table
#Populating the table
cursor.execute(   INSERT INTO EMPLOYEE
   (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES 
   ( Ramya ,  Rama priya , 27,  F , 9000),
   ( Vinay ,  Battacharya , 20,  M , 6000), 
   ( Sharukh ,  Sheik , 25,  M , 8300), 
   ( Sarmista ,  Sharma , 26,  F , 10000),
   ( Tripthi ,  Mishra , 24,  F , 6000)   )
conn.commit()

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

#Commit your changes in the database
conn.commit()

#Closing the connection
conn.close()

Output

[( Vinay ,  Battacharya , 20,  M , 6000, None),
   ( Tripthi ,  Mishra , 24,  F , 6000, None),
   ( Sharukh ,  Sheik , 25,  M , 8300, None),
   ( Sarmista ,  Sharma , 26,  F , 10000, None),
   ( Ramya ,  Rama priya , 27,  F , 9000, None)]