SQLite Order By Query

SQLite

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

Read more on Order By here.

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


plus2net.com



Post your comments , suggestion , error , requirements etc here




We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2020 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer