Session Prices

Feel free to negotiate depending on your situation.

Scheduling

Hit me up to work out a scheduled time and date.
[email protected]
213-995-7078 text or voice
Search Limits

Sometimes, we only want to return a subset of rows found. Let's pretend we have a database with 1,000,000 rows in it. We obviously don't want to return all of those rows. We only want to return a small subset of them. To do this, we need to use the limit statement.

Why do we not want to retrieve all rows? There are a myriad of reasons. For example, it might look cleaner and nicer presenting a subset of the rows to a user in a web interface. It also saves memory and it takes less time to transfer a small subset, rather than the entire table.

In my names table I have seven rows. Let's say I only want to get 5 back.

mysql> select * from names limit 0,5;
+----+---------------------+-----------+-----------+-----------+
| id | started             | lastName  | firstName | salary    |
+----+---------------------+-----------+-----------+-----------+
|  1 | 2019-01-01 00:00:00 | Smith     | Jim       | 120000.01 |
|  2 | 2019-01-01 00:00:00 | Rocker    | Bob       | 120000.01 |
|  3 | 2019-12-21 00:00:00 | Kella     | Shella    |  65000.28 |
|  4 | 2019-12-21 13:31:39 | Toga      | Sara      |  78000.44 |
|  5 | 2019-05-01 00:00:00 | Flauntana | Hannah    |  96468.97 |
+----+---------------------+-----------+-----------+-----------+
5 rows in set (0.00 sec)

How is this working? You see limit 0,5. The first number is the start row that is being returned. The second number is how many results we want back aka 5 rows.

How do we get the final two rows of the set?

mysql> select * from names limit 5,5;
+----+---------------------+----------+-----------+----------+
| id | started             | lastName | firstName | salary   |
+----+---------------------+----------+-----------+----------+
|  6 | 2019-06-01 00:00:00 | Found    | Lost      | 65732.86 |
|  7 | 2019-07-01 00:00:00 | Donkey   | Man       |     1.99 |
+----+---------------------+----------+-----------+----------+
2 rows in set (0.00 sec)

The second query has a limit of limit 5,5. The first number describes that we want to start at row five and return five more rows. If there aren't five more rows, that's okay. It will return whatever is left within the set.

How do we use limit with joins?

mysql> select lastName, firstName, zip from names n left join addresses a on n.id=a.nameId limit 0,5;
+-----------+-----------+-------+
| lastName  | firstName | zip   |
+-----------+-----------+-------+
| Smith     | Jim       | 10000 |
| Rocker    | Bob       | 90000 |
| Kella     | Shella    | 50000 |
| Toga      | Sara      | 60000 |
| Flauntana | Hannah    | 40000 |
+-----------+-----------+-------+
5 rows in set (0.00 sec)

In the previous query, we aren't using a where statement. Let's add one. We want to find any employee with a salary greater than $2.00 per year.

mysql> select lastName, firstName, zip, salary from names n left join addresses a on n.id=a.nameId where salary > 2.00 limit 0,5;
+-----------+-----------+-------+-----------+
| lastName  | firstName | zip   | salary    |
+-----------+-----------+-------+-----------+
| Smith     | Jim       | 10000 | 120000.01 |
| Rocker    | Bob       | 90000 | 120000.01 |
| Kella     | Shella    | 50000 |  65000.28 |
| Toga      | Sara      | 60000 |  78000.44 |
| Flauntana | Hannah    | 40000 |  96468.97 |
+-----------+-----------+-------+-----------+
5 rows in set (0.00 sec)

In this last query, what happens if we go to the second set of rows?

mysql> select lastName, firstName, zip, salary from names n left join addresses a on n.id=a.nameId where salary > 2.00 limit 5,5;
+----------+-----------+-------+----------+
| lastName | firstName | zip   | salary   |
+----------+-----------+-------+----------+
| Found    | Lost      | 90001 | 65732.86 |
+----------+-----------+-------+----------+
1 row in set (0.00 sec)

We only got one more row, instead of two. That is because Donkey Man only has a salary of $1.99 and the query excludes that row. Poor Donkey Man. He broke and don't know it. 8-D

One last example. We need to order by salary. How does that work with limit?

mysql> select lastName, firstName, zip, salary from names n left join addresses a on n.id=a.nameId where salary > 2.00 order by salary desc limit 0,5;
+-----------+-----------+-------+-----------+
| lastName  | firstName | zip   | salary    |
+-----------+-----------+-------+-----------+
| Smith     | Jim       | 10000 | 120000.01 |
| Rocker    | Bob       | 90000 | 120000.01 |
| Flauntana | Hannah    | 40000 |  96468.97 |
| Toga      | Sara      | 60000 |  78000.44 |
| Found     | Lost      | 90001 |  65732.86 |
+-----------+-----------+-------+-----------+
5 rows in set (0.00 sec)

The one thing to remember about limit is that you need to put it at the very end of your query.