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 moduleimport sqlite3 # create connection to the database # geeks_databaseconnection = sqlite3.connect('geeks_database.db') # create table named address of customers # with 4 columns id,name age and addressconnection.execute('''CREATE TABLE customer_address (ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50)); ''') # close the connectionconnection.close() |
Output:
Now, Insert 5 records into the customer_address table.
Python3
# importing sqlite moduleimport sqlite3 # create connection to the database # geeks_databaseconnection = sqlite3.connect('geeks_database.db') # insert records into tableconnection.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 connectionconnection.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 moduleimport sqlite3 # create connection to the database# geeks_databaseconnection = 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 rowfor i in cursor: print(i) # close the connectionconnection.close() |
Output:
Example 2: Display address and id based on the address in descending order.
Python3
# importing sqlite moduleimport sqlite3 # create connection to the database # geeks_databaseconnection = sqlite3.connect('geeks_database.db') # sql query to display address and id# based on address in descending ordercursor = connection.execute( "SELECT ADDRESS,ID from customer_address ORDER BY address DESC") # display data row by rowfor i in cursor: print(i) # close the connectionconnection.close() |
Output:
Example 3: Display name and id based on name in descending order
Python3
# importing sqlite moduleimport sqlite3 # create connection to the database # geeks_databaseconnection = sqlite3.connect('geeks_database.db') # sql query to display name and id based# on name in descending ordercursor = connection.execute( "SELECT NAME,ID from customer_address ORDER BY NAME DESC") # display data row by rowfor i in cursor: print(i) # close the connectionconnection.close() |
Output:
Please Login to comment...