Structured Query Language (SQL)

Now that we know some stuff about dates, it would be cool to Sort by Dates.

We're going to use the order by keywords in our next select:

mysql> select * from names order by started;
+----+---------------------+----------+-----------+
| id | started             | lastName | firstName |
+----+---------------------+----------+-----------+
|  1 | 2019-01-01 00:00:00 | Smith    | Jim       |
|  2 | 2019-01-01 00:00:00 | Rocker   | Bob       |
|  3 | 2019-12-21 00:00:00 | Kella    | Shella    |
|  4 | 2019-12-21 13:31:39 | Toga     | Sara      |
+----+---------------------+----------+-----------+
4 rows in set (0.00 sec)

Didn't really change much from our standard query without order by. Let's try again, by adding the desc (Descending) keyword. Basically, we can order our sorts by Ascending asc or Descending desc. By default, our sorts will be ordered by asc. We don't need to specify it. Let's see what happens when we use desc:

mysql> select * from names order by started desc;
+----+---------------------+----------+-----------+
| id | started             | lastName | firstName |
+----+---------------------+----------+-----------+
|  4 | 2019-12-21 13:31:39 | Toga     | Sara      |
|  3 | 2019-12-21 00:00:00 | Kella    | Shella    |
|  1 | 2019-01-01 00:00:00 | Smith    | Jim       |
|  2 | 2019-01-01 00:00:00 | Rocker   | Bob       |
+----+---------------------+----------+-----------+
4 rows in set (0.00 sec)

It's basically backwards. Since the dates are the same for Bob and Jim, MySQL just does whatever it wants. No biggie.

Let's try sorting by lastName:

mysql> select * from names order by lastName;
+----+---------------------+----------+-----------+
| id | started             | lastName | firstName |
+----+---------------------+----------+-----------+
|  3 | 2019-12-21 00:00:00 | Kella    | Shella    |
|  2 | 2019-01-01 00:00:00 | Rocker   | Bob       |
|  1 | 2019-01-01 00:00:00 | Smith    | Jim       |
|  4 | 2019-12-21 13:31:39 | Toga     | Sara      |
+----+---------------------+----------+-----------+
4 rows in set (0.01 sec)

I'm sure you can imagine what using desc would look like with this query.

You can also sort using joins. Let's do it!!!

Right now, in my addresses table, I have:

mysql> select * from addresses;
+----+--------+--------------+-------------+-------+-------+
| id | nameId | street       | city        | state | zip   |
+----+--------+--------------+-------------+-------+-------+
|  1 |      1 | 1234 Main St | Los Angeles | CA    | 10000 |
+----+--------+--------------+-------------+-------+-------+
1 row in set (0.00 sec)

I'm going to add a few more rows:

mysql> insert into addresses (nameId, street, city, state, zip) values 
(2, "5678 North Dr", "Polka", "CA", "90000"),
(3, "543 Asdf Wy", "Visia", "OR", "50000"),
(4, "56743 Iso Dr", "Camden", "WA", "60000");

I'm using the MySQL bulk insert feature above. The values must match the order in which you described the field names. For example, if nameId comes first and street comes second, the values must be set as 2, "street address"...

mysql> select * from addresses;
+----+--------+---------------+-------------+-------+-------+
| id | nameId | street        | city        | state | zip   |
+----+--------+---------------+-------------+-------+-------+
|  1 |      1 | 1234 Main St  | Los Angeles | CA    | 10000 |
|  2 |      2 | 5678 North Dr | Polka       | CA    | 90000 |
|  3 |      3 | 543 Asdf Wy   | Visia       | OR    | 50000 |
|  4 |      4 | 56743 Iso Dr  | Camden      | WA    | 60000 |
+----+--------+---------------+-------------+-------+-------+
4 rows in set (0.00 sec)

That looks better. Now, let's do a join and sort by zip:

mysql> select started, lastName, firstName, zip from names n left join addresses a on n.id=a.nameId order by zip;
+---------------------+----------+-----------+-------+
| started             | lastName | firstName | zip   |
+---------------------+----------+-----------+-------+
| 2019-01-01 00:00:00 | Smith    | Jim       | 10000 |
| 2019-12-21 00:00:00 | Kella    | Shella    | 50000 |
| 2019-12-21 13:31:39 | Toga     | Sara      | 60000 |
| 2019-01-01 00:00:00 | Rocker   | Bob       | 90000 |
+---------------------+----------+-----------+-------+
4 rows in set (0.00 sec)

Let's try doing a sort with a where condition:

mysql> select started, lastName, firstName, zip from names n left join addresses a on n.id=a.nameId 
where lastName='Smith' order by zip;
+---------------------+----------+-----------+-------+
| started             | lastName | firstName | zip   |
+---------------------+----------+-----------+-------+
| 2019-01-01 00:00:00 | Smith    | Jim       | 10000 |
+---------------------+----------+-----------+-------+
1 row in set (0.00 sec)

We only got one row back, because we were pretty specific with our search.

Just remember that order by always comes after your where statement. If there is no where statement, it would come after the from statement. For example:

select * from names order by lastName;

SQL

Session Prices

Scheduling