-
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
-
Union & Sort
The use of UNION puts together results. For example, in the below code, we rename both table columns so that we return values for ID and Grade. What you will notice is that we have replicated ID numbers, and no way to tell what grade goes to what class.
#Union combines two results, if we rename the columns to be ID and Grade for both, then we can get the following
query = """SELECT StudentID AS ID,
Grade1 AS Grade
FROM Class1
UNION
SELECT StudentID AS ID,
Grade2 AS Grade
FROM Class2;"""
df = pd.read_sql(query, conn)
print(df)
Something that can be very helpful is that we are allowed to define constants in SELECT statements. For example, putting “1 as Class” will add a column where all values are 1. If we do UNION but define class as 1 and 2 depending on which side of the UNION, we can get a good identifier.
#Of course we might want a way to denote the classroom, we can give the constants 1 and 2 for class
query = """SELECT StudentID AS ID,
Grade1 AS Grade,
1 AS Class
FROM Class1
UNION
SELECT StudentID AS ID,
Grade2 AS Grade,
2 AS Class
FROM Class2;"""
df = pd.read_sql(query, conn)
print(df)
Another useful technique is using ORDER BY which sorts our results based upon whatever follows it.
#ORDER BY will sort the table by a given column
query = """SELECT * FROM Class1 ORDER BY Grade1;"""
df = pd.read_sql(query, conn)
print(df)
While the default behavior is to sort the values in ascending order, we can follow up ORDER BY Grade1 with either ASC or DESC to define whether the sort should be descending or ascending.
#Passing the keyword ASC/DESC will change the sorting to either be ascending or descending
query = """SELECT * FROM Class1 ORDER BY Grade1 DESC;"""
df = pd.read_sql(query, conn)
print(df)
In addition to sorting on one variable, it also is possible to sort on two variables (where whenever there are ties in the first variable, a second variable is used to further sort).
#You can also sort by first one column then another
query = """SELECT * FROM Class1 ORDER BY Grade1, Absent1;"""
df = pd.read_sql(query, conn)
print(df)
And using DESC/ASC right after each variable name gives the option to choose different sorting behavior for each of the variables.
#And as well you are allowed to pick the sorting direction for each
query = """SELECT * FROM Class1 ORDER BY Grade1 DESC, Absent1 ASC;"""
df = pd.read_sql(query, conn)
print(df)