Python sqlite order by query to manage record in Ascending or descending order of a table

We are using our student table. Check how to create database and student table here.

Read more on Order By here.

Video Tutorial on SQLite ORDER BY query

USING Order by query

Order by query will take one or more column as input. We will display based on class column. As class column is storing text data, we will display rows in alphabetical order of class column. Starting from a to z. We will use order by with SELECT query.

q="SELECT id,name,class,mark,sex FROM student ORDER BY class"
my_cursor=my_conn.execute(q)

for row in my_cursor:
    print(row[0],row[1],row[2],row[3],row[4])

Output

23 Herod Eight 79 male
7 My John Rob Five 78 male
8 Asruid Five 85 male
18 Honny Five 75 male
1 John Deo Four 75 female
------------
------------

Based on alphabetical forward order (from A to Z ) we displayed records based on class column in above code. We can reverse the order ( from Z to A ) by using DESC. In above code we changed the query part only by adding DESC.

q="SELECT id,name,class,mark,sex FROM student ORDER BY class DESC"

Output

2 Max Ruin Three 85 male
3 Arnold Three 55 male
27 Big Nose Three 81 female
9 Tes Qry Six 78 male
----------------
----------------

Using error handling

We can display error message if there is any error by using try except . In our query we will use two columns class and mark. So first the order will be using class column and then within the class column mark is used from lowest ( mark ) to highest ( mark ) to order the records.

q="SELECT * FROM  student ORDER BY class,mark  "
try:
    my_cursor=my_conn.execute(q)
    
    for row in my_cursor:
        print(row[0],row[1],row[2],row[3],row[4])
except sqlite3.Error as my_error:
  print("error: ",my_error)

Output

23 Herod Eight 79 male
18 Honny Five 75 male
7 My John Rob Five 78 male
8 Asruid Five 85 male
6 Alex John Four 55 male
10 Big John Four 55 female
4 Krish Star Four 60 female
5 John Mike Four 60 female
21 Babby John Four 69 female
1 John Deo Four 75 female
15 Tade Row Four 88 male
16 Gimmy Four 88 male
31 Marry Toeey Four 88 male
------------------
-----------------

Let us change the query to q="SELECT * FROM student ORDER BY class1,mark" , Note that there is no column as class1. Here is the output or error message we will get.

error:  no such column: class1

Using ASC and DESC

We can use ASC for ascending order and DESC for descending order against each columns. Here is the query part only. By default the order is always ASC ( Ascending ).

Both class and mark columns are in descending order.

q="SELECT * FROM  student ORDER BY class DESC,mark DESC"

class is in ascending and mark is in descending order

q="SELECT * FROM  student ORDER BY class ASC,mark DESC"

class is descending and mark in Ascending order

q="SELECT * FROM  student ORDER BY class DESC,mark"

Sqlite Connection insert Delete update select

Numpy Pandas Python & MySQL Python- Tutorials

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

SQLite Order By Query

Video Tutorial on SQLite ORDER BY query

USING Order by query

Using error handling

Using ASC and DESC

Subhendu Mohapatra