How to use limit and delete in SQL in 8 steps

This article discusses how to use the LIMIT clause and DELETE data form table.

Usually, tables in a production environment have thousands or millions of rows and a select query may return several hundred matched rows. This is problematic because outputting thousands of rows on the console or on a network connection can overwhelm the end-user in the former and is impractical in the latter scenario. The LIMIT clause allows us to restrict the number of rows returned from the result of a select query.

Example Syntax :
SELECT col1, col2, ... coln
FROM table
WHERE col3 LIKE "%some-string%"
ORDER BY col3
LIMIT 10;

-- The lesson queries are reproduced below for convenient copy/paste into the terminal.
-- Query 1
SELECT FirstName, SecondName from Actors ORDER BY NetWorthInMillions DESC LIMIT 3;
-- Query 2
SELECT FirstName, SecondName from Actors ORDER BY NetWorthInMillions DESC LIMIT 4 OFFSET 3;
-- Query 3
SELECT FirstName, SecondName from Actors ORDER BY NetWorthInMillions DESC LIMIT 3,4;
-- Query 4
SELECT FirstName, SecondName from Actors ORDER BY NetWorthInMillions DESC LIMIT 1000 OFFSET 3;
-- Query 5
SELECT FirstName, SecondName from Actors ORDER BY NetWorthInMillions DESC LIMIT 1844674407370;

SELECT column1, column2 from table ORDER BY column3 DESC LIMIT 3;

Say we want to find the top three actors by net worth. We can execute the following query to get the desired result:

SELECT FirstName, SecondName from Actors ORDER BY NetWorthInMillions DESC LIMIT 3;

SELECT column1, column2 from table ORDER BY column3 DESC LIMIT 4 OFFSET 3;

Next, say we are required to retrieve the next 4 richest actors after the top three. We can do so by specifying the number of rows we want after the top three rows using the OFFSET keyword.

SELECT FirstName, SecondName from Actors ORDER BY NetWorthInMillions DESC LIMIT 4 OFFSET 3;

We can also use the alternative syntax as follows:

SELECT FirstName, SecondName from Actors ORDER BY NetWorthInMillions DESC LIMIT 3,4;

The syntax is:
LIMIT <offset>, <number_of_row_to_print>;

SELECT column1, column2 from table ORDER BY column3 DESC LIMIT 1000 OFFSET 3;

Note that we can specify as many rows as we would like to be retrieved, starting at the offset, we specify. For instance, we can ask for a thousand rows after the offset and we’ll be returned all the rows after the top three.

SELECT FirstName, SecondName from Actors ORDER BY NetWorthInMillions DESC LIMIT 1000 OFFSET 3;

The maximum number we can specify after the LIMIT keyword is 18446744073709551615, since that is the maximum value that can be stored in MySQL’s unsigned BIGINT variable type. Any value higher than that and MySQL will complain.

SELECT FirstName, SecondName from Actors ORDER BY NetWorthInMillions DESC LIMIT 18446744073709551616;

DELETING DATA

We can delete rows from a table using the DELETE statement. A delete statement deletes an entire row and not individual columns. If changing a particular column value for a row is desired, use the UPDATE statement, which we will cover next. Also, realize that deleting all the rows of a table doesn’t delete the table itself.

Example Syntax :
DELETE FROM table
WHERE col3 > 5
ORDER BY col1
LIMIT 5;

-- The article queries are reproduced below for convenient copy/paste into the terminal.
-- Query 1
DELETE FROM Actors WHERE FirstName="priyanka";
-- Query 2
DELETE FROM Actors WHERE Gender="Male";
-- Query 3
DELETE FROM Actors ORDER BY NetWorthInMillions DESC LIMIT 3;
-- Query 4
DELETE FROM Actors;

DELETE FROM table WHERE column=”condition”;

We’ll start with deleting just one row. In order to target a single row, we’ll need to use the WHERE clause similar to how we used it with a select statement. Let’s delete the row for the actress priyanka.

DELETE FROM Actors WHERE FirstName="priyanka";

Note that the string comparison for the FirstName performed by MySQL doesn’t take case into account and if we specified the WHERE clause with an uppercase as FirstName=”PRIYANKA” the row would still be deleted.

DELETE FROM table WHERE Gender=”Male”;

The delete statement will delete all the matching rows, which in the previous example is only one. As an example, we can write a query to delete all male actors as follows:

DELETE FROM Actors WHERE Gender="Male";

DELETE FROM table ORDER BY column DESC LIMIT 3;

Suppose that out of the remaining female actors in our database, we want to delete the top three actresses by net worth. We can accomplish that by using the ORDER BY and LIMIT clauses.

DELETE FROM Actors ORDER BY NetWorthInMillions DESC LIMIT 3;

The above query removes the top three actresses by net worth.

DELETE FROM table;

We can also remove all the rows from a table using the following query:

DELETE FROM Actors;

Notice that the table can still be queried even after we have removed all the rows from it.