MySQL Select Top N Rows

Sometimes you may need to select top 1 row or top 10 rows in MySQL table. Here’s how to select top N rows in MySQL. You can use these SQL queries to get most recent transactions, or purchases for your business.

MySQL Select Top N Rows

Here are the steps to select top N rows in MySQL using LIMIT clause. Here’s the syntax to select top N rows in MySQL.

select column1, column2, ... from table_name LIMIT n

In the above statement, we list the columns column1, column2, … that you want to select in your query. Also, you need to specify LIMIT n after the table name, where n is the number of rows you want to select.

The above query will select top n records in your table. If you want to select these rows from a specific location, say row 100, then here’s the syntax.

select column1, column2, ... from table_name LIMIT m,n

In the above query, you need to specify the starting position m after which n rows need to be selected

Bonus Read : MySQL Remove Duplicate Records

Let’s look at a few common examples for the same. Let’s say you have the following table sales(order_date, sale)

mysql> select * from sales;
+------------+------+
| order_date | sale |
+------------+------+
| 2020-05-04 |  230 |
| 2020-05-05 |  200 |
| 2020-05-06 |  210 |
|        ... |  ... |
| 2020-06-09 |  290 |
| 2020-06-10 |  230 |
| 2020-06-11 |  210 |
+------------+------+

MySQL Select top 1 row

Here’s the SQL query to select top 1 row.

mysql> select * from sales limit 1;
+------------+------+
| order_date | sale |
+------------+------+
| 2020-05-04 |  230 |
+------------+------+

Here’s the SQL query to select top 1 row starting from row 2

mysql> select * from sales limit 2,1;
+------------+------+
| order_date | sale |
+------------+------+
| 2020-05-06 |  210 |
+------------+------+

Bonus Read : How to Execute Stored Procedure in MySQL

MySQL Select top 10 rows

Here’s the SQL query to select top 10 rows.

mysql> select * from sales limit 10;
+------------+------+
| order_date | sale |
+------------+------+
| 2020-05-04 |  230 |
| 2020-05-05 |  200 |
| 2020-05-06 |  210 |
| 2020-05-07 |  180 |
| 2020-05-08 |  220 |
| 2020-05-09 |  230 |
| 2020-05-10 |  220 |
| 2020-05-11 |  225 |
| 2020-05-12 |  200 |
| 2020-05-13 |  210 |
+------------+------+

Here’s the SQL query to select top 10 row starting from row 2

mysql> select * from sales limit 2,10;
+------------+------+
| order_date | sale |
+------------+------+
| 2020-05-06 |  210 |
| 2020-05-07 |  180 |
| 2020-05-08 |  220 |
| 2020-05-09 |  230 |
| 2020-05-10 |  220 |
| 2020-05-11 |  225 |
| 2020-05-12 |  200 |
| 2020-05-13 |  210 |
| 2020-05-14 |  190 |
| 2020-05-15 |  200 |
+------------+------+

Bonus Read : MySQL Get Duplicate Records

MySQL Select Top 1 order by desc

Here’s the SQL query to select top 1 row with highest sale value using ORDER BY clause along with LIMIT keyword

mysql> select * from sales order by sale desc limit 10;
+------------+------+
| order_date | sale |
+------------+------+
| 2020-06-09 |  290 |
+------------+------+

MySQL Select Top 10 highest values

Here’s the SQL query to select top 10 highest values

mysql> select * from sales order by sale desc limit 10;
+------------+------+
| order_date | sale |
+------------+------+
| 2020-06-09 |  290 |
| 2020-06-07 |  270 |
| 2020-06-06 |  260 |
| 2020-05-20 |  250 |
| 2020-05-22 |  245 |
| 2020-06-08 |  240 |
| 2020-05-21 |  240 |
| 2020-06-01 |  237 |
| 2020-05-30 |  235 |
| 2020-05-31 |  233 |
+------------+------+

Bonus Read : MySQL Add Unique Constraint

MySQL Select Top 10 distinct

Here’s the SQL query to select top 10 distinct rows using DISTINCT keyword.

mysql> select distinct * from sales limit 10;

Hopefully, now you can easily select top N rows in MySQL.

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

Sreeram Sreenivasan

Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.