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.
# importing sqlite module import sqlite3
# create connection to the database # geeks_database connection = sqlite3.connect( 'geeks_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.
# importing sqlite module import sqlite3
# create connection to the database # geeks_database connection = sqlite3.connect( 'geeks_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.
# importing sqlite module import sqlite3
# create connection to the database # geeks_database connection = sqlite3.connect( 'geeks_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.
# importing sqlite module import sqlite3
# create connection to the database # geeks_database connection = sqlite3.connect( 'geeks_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
# importing sqlite module import sqlite3
# create connection to the database # geeks_database connection = sqlite3.connect( 'geeks_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: