Python SQLite – ORDER BY Clause
In this article, we will discuss ORDER BY clause in SQLite using Python. The ORDER BY statement is a SQL statement that is used to sort the data in either ascending or descending according to one or more columns. By default, ORDER BY sorts the data in ascending order.
- DESC is used to sort the data in descending order.
- ASC to sort in ascending order.
Syntax: SELECT column1,column2,., column n FROM table_name ORDER BY column_name ASC|DESC;
First, let’s create a database.
Python3
# importing sqlite module import sqlite3 # create connection to the database # Beginner_database connection = sqlite3.connect( 'Beginner_database.db' ) # create table named address of customers # with 4 columns id,name age and address connection.execute( '''CREATE TABLE customer_address (ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50)); ''' ) # close the connection connection.close() |
Output:
Now, Insert 5 records into the customer_address table.
Python3
# importing sqlite module import sqlite3 # create connection to the database # Beginner_database connection = sqlite3.connect( 'Beginner_database.db' ) # insert records into table connection.execute( "INSERT INTO customer_address VALUES (1, 'nikhil teja', 22, 'hyderabad' )" ) connection.execute( "INSERT INTO customer_address VALUES (2, 'karthik', 25, 'khammam')" ) connection.execute( "INSERT INTO customer_address VALUES (3, 'sravan', 22, 'ponnur' )" ) connection.execute( "INSERT INTO customer_address VALUES (4, 'deepika', 25, 'chebrolu' )" ) connection.execute( "INSERT INTO customer_address VALUES (5, 'jyothika', 22, 'noida')" ) # close the connection connection.close() |
Output:
After creating the database and adding data to it let’s see the use of order by clause.
Example 1: Display all details from the table in ascending order(default) based on address.
Python3
# importing sqlite module import sqlite3 # create connection to the database # Beginner_database connection = sqlite3.connect( 'Beginner_database.db' ) # sql query to display all details from # table in ascending order based on address. cursor = connection.execute( "SELECT ADDRESS,ID from customer_address ORDER BY address DESC" ) # display data row by row for i in cursor: print (i) # close the connection connection.close() |
Output:
Example 2: Display address and id based on the address in descending order.
Python3
# importing sqlite module import sqlite3 # create connection to the database # Beginner_database connection = sqlite3.connect( 'Beginner_database.db' ) # sql query to display address and id # based on address in descending order cursor = connection.execute( "SELECT ADDRESS,ID from customer_address ORDER BY address DESC" ) # display data row by row for i in cursor: print (i) # close the connection connection.close() |
Output:
Example 3: Display name and id based on name in descending order
Python3
# importing sqlite module import sqlite3 # create connection to the database # Beginner_database connection = sqlite3.connect( 'Beginner_database.db' ) # sql query to display name and id based # on name in descending order cursor = connection.execute( "SELECT NAME,ID from customer_address ORDER BY NAME DESC" ) # display data row by row for i in cursor: print (i) # close the connection connection.close() |
Output:
Contact Us