JIYIK CN >

Sometimes, you have to select first N rows of MySQL database according to your project requirements.

nThe value of varies according to the requirement; it can be TOP 1 row or TOP 30 rows. We will learn how to select top N rows using the clause in MySQL database LIMIT.

You can benefit from these queries by using them in pagination, finding recent purchases or transactions. It is also important to note that each database has a different syntax to accomplish this functionality.

SQL Server uses SELECT TOP, MySQL uses LIMIT, and Oracle uses ROWNUMand FETCH FIRST n ROWS ONLY.

LIMITSelect top N rows in MySQL using clause

Using Top N queries means that you want to limit the results to a certain number of rows. These are used to get the best or most recent rows from the result set.

In this tutorial, we used a customertable called , which contains customer_id, customer_firstname, customer_lastnameand customer_age. We populated this customertable with some data that currently looks like this.

We will use customertable to practice in MySQL LIMIT. We want customerto select TOP 3 records from the table.

Sample code:

SELECT * FROM customer LIMIT 3;

Output:

We want to get the three youngest customers and analyze them to make predictions. You can see the following code example.

Sample code:

SELECT * FROM customer
ORDER BY customer_age ASC
LIMIT 3;

First, the table data will customer_agebe sorted by . The youngest 客户will be at the top and the oldest will be at the bottom.

Then select the first 3 rows from the result set of ordered data. See the following screenshot.

Output:

3What if you want to select the first 4 rows starting from offset ? You can do that using the following syntax.

SELECT * FROM you_table_name
ORDER BY column_name ASC
LIMIT offset,row_count;

Always remember that LIMITthe offset of the first row in a clause 0starts at . Here is the sample code for this scenario.

Sample code:

SELECT * FROM customer
ORDER BY customer_age ASC
LIMIT 3,4;

Output:

Alternatively, we can also use the clause with the row number and offset in the following way LIMIT.

# You can also write the above query in this way
SELECT * FROM person.customer
ORDER BY customer_age ASC
LIMIT 1 OFFSET 4;

Output:

You can also sort your table in descending order by ASCreplacing in your query with .DESC

in conclusion

We concluded that we can limit the number of rows in the result set based on the project requirements.

We can choose either first n rows or first n rows starting from m. We also learned that LIMITthe clause can be used with or without ORDER BY.