SQLite DISTINCT Query


SQLite

We are using our student table for the examples here . You can create or download the SQLite database with sample records here.

  • Video Tutorial on SQLite DISTINCT Query



Read more on MySQL distinct query here.

SELECT DISTINCT(Column_name) FROM table
In our sample database my_db.db, we have student table. Each row ( student ) have one class. We will display the unique classes by using DISTINCT query.
q="SELECT DISTINCT(class) FROM  student"
try:
    my_cursor=my_conn.execute(q)
    
    for row in my_cursor:
        print(row[0])
except sqlite3.Error as my_error:
  print("error: ",my_error)
Output
Four
Three
Five
Six
Seven
Nine
Eight
We can add ORDER BY to this to display in alphabetical order. ( the change in query is shown here )
q="SELECT DISTINCT(class) FROM  student ORDER BY class"

Using COUNT()

How many unique classes are there in our student table ?
q="SELECT COUNT(DISTINCT(class)) FROM  student"
Output
7
Using SQLAlchemy and with WHERE condition
from sqlalchemy.exc import SQLAlchemyError
q="SELECT DISTINCT(class) FROM student WHERE mark>=80"
try:
  r_set=my_conn.execute(q)
  data=r_set.fetchall()
  for row in data:
    print(row[0])
except SQLAlchemyError as e:
  error=str(e.__dict__['orig'])
  print(error)
else:
  print("No of records displayed : ",len(data))
Output
Three
Five
Six
Seven
Four
No of records displayed :  5

Using two columns

We can get distinct or unique values using two columns.
q="SELECT DISTINCT class,sex FROM  student"
try:
    my_cursor=my_conn.execute(q)
    
    for row in my_cursor:
        print(row[0],row[1])
except sqlite3.Error as my_error:
  print("error: ",my_error)
Output
Four female
Three male
Four male
Five male
Six male
Six female
Seven female
Nine male
Nine female
Eight male
Seven male
Three female

Handling null data

Null value in a column is also considered as one DISTINCT value. So our result set will contain one row or record for NULL value. If we want not to included NULL value then we can exclude those records by using NOT NULL.
q="SELECT DISTINCT class FROM  student WHERE class NOT NULL"
try:
    my_cursor=my_conn.execute(q)
    
    for row in my_cursor:
        print(row[0])
except sqlite3.Error as my_error:
  print("error: ",my_error)
View and Download sqlite-distinct.ipynb file

Sqlite Connection insert Delete Order By


plus2net.com



Post your comments , suggestion , error , requirements etc here





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