- Python SQLite - Discussion
- Python SQLite - Useful Resources
- Python SQLite - Quick Guide
- Python SQLite - Cursor Object
- Python SQLite - Join
- Python SQLite - Limit
- Python SQLite - Drop Table
- Python SQLite - Delete Data
- Python SQLite - Update Table
- Python SQLite - Order By
- Python SQLite - Where Clause
- Python SQLite - Select Data
- Python SQLite - Insert Data
- Python SQLite - Create Table
- Python SQLite - Establishing Connection
- Python SQLite - Introduction
- Python SQLite - Home
Selected Reading
- Who is Who
- Computer Glossary
- HR Interview Questions
- Effective Resume Writing
- Questions and Answers
- UPSC IAS Exams Notes
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) ]Advertisements