-
Basic Functionality 6
-
Lecture1.1
-
Lecture1.2
-
Lecture1.3
-
Lecture1.4
-
Lecture1.5
-
Lecture1.6
-
-
Pandas & Indices 4
-
Lecture2.1
-
Lecture2.2
-
Lecture2.3
-
Lecture2.4
-
-
Joins, Unions & Order By 3
-
Lecture3.1
-
Lecture3.2
-
Lecture3.3
-
-
Group By & Views 3
-
Lecture4.1
-
Lecture4.2
-
Lecture4.3
-
Distinct
The use of DISTINCT returns unique values only. The following will limit our results to only distinct values of ID. Notice we are giving the column name directly after DISTINCT.
#DISTINCT allows for the unique values
cur.execute('SELECT DISTINCT ID FROM students;')
data = cur.fetchall()
print(data)
Let’s duplicate a row to prove a point.
#Let's duplicate a row
query = """INSERT INTO students
VALUES ('Tom', 90, 5);"""
cur.execute(query)
Now, when we call DISTINCT *, we are returning all distinct combinations of the columns. So repeat column values do not get filtered as long as there is no exact match between all columns in two entries.
#With select * we get all the records
cur.execute('SELECT * FROM students;')
data = cur.fetchall()
print(data)
#With select DISTINCT * we get rid of any duplicates where all the columns match
cur.execute('SELECT DISTINCT * FROM students;')
data = cur.fetchall()
print(data)
Add in a row where the grade is slightly different, but the other two columns are the same.
#Let's duplicate a row except with grades different
query = """INSERT INTO students
VALUES ('Tom', 85, 5);"""
cur.execute(query)
Now you can see that we can query for distinct combinations of any given columns. This will return different results from DISTINCT *.
#You can also delete specific records with DELETE
cur.execute('DELETE FROM students WHERE name=="Tom";')
cur.execute('SELECT * FROM students;')
data = cur.fetchall()
print(data)
Prev
Update
Next
Pandas to SQL