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!
