{"id":13736,"date":"2023-12-29T00:30:13","date_gmt":"2023-12-29T07:30:13","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=13736"},"modified":"2024-01-19T19:07:33","modified_gmt":"2024-01-20T02:07:33","slug":"mysql-limit","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-limit\/","title":{"rendered":"MySQL LIMIT"},"content":{"rendered":"\n<p><strong>Summary<\/strong><em>: <\/em>in this tutorial, you will learn how to use the MySQL <code>LIMIT<\/code>&nbsp;clause to constrain the number of rows returned by a query.<em> <\/em><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Introduction to MySQL LIMIT clause<\/h2>\n\n\n\n<p>The <code>LIMIT<\/code> clause is used in the <code><a href=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-select-from\/\">SELECT<\/a><\/code> statement to constrain the number of rows to return. The <code>LIMIT<\/code> clause accepts one or two arguments. The values of both arguments must be zero or positive <a href=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-int\/\">integers<\/a>.<\/p>\n\n\n\n<p>The following illustrates the <code>LIMIT<\/code> clause syntax with two arguments:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> \n    select_list\n<span class=\"hljs-keyword\">FROM<\/span>\n    table_name\n<span class=\"hljs-keyword\">LIMIT<\/span> &#91;<span class=\"hljs-keyword\">offset<\/span>,] <span class=\"hljs-keyword\">row_count<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In this syntax:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>The <code>offset<\/code> specifies the offset of the first row to return. The <code>offset<\/code> of the first row is 0, not 1.<\/li>\n\n\n\n<li>The <code>row_count<\/code> specifies the maximum number of rows to return.<\/li>\n<\/ul>\n\n\n\n<p>The following picture illustrates the <code>LIMIT<\/code> clause:<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img loading=\"lazy\" decoding=\"async\" width=\"445\" height=\"240\" src=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/08\/MySQL-LIMIT-illustration.png\" alt=\"\" class=\"wp-image-8152\" srcset=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/08\/MySQL-LIMIT-illustration.png 445w, https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/08\/MySQL-LIMIT-illustration-200x108.png 200w\" sizes=\"auto, (max-width: 445px) 100vw, 445px\" \/><\/figure>\n<\/div>\n\n\n<p>When you use the&nbsp;<code>LIMIT<\/code> clause with one argument, MySQL will use this argument to determine the maximum number of rows to return from the first row of the result set.<\/p>\n\n\n\n<p>Therefore, these two clauses are equivalent:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">LIMIT row_count;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>And<\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">LIMIT 0 , row_count;<\/code><\/span><\/pre>\n\n\n<p>In addition to the above syntax, MySQL provides the following alternative <code>LIMIT<\/code> clause syntax:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">LIMIT row_count OFFSET offset<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">The LIMIT and ORDER BY clauses<\/h3>\n\n\n\n<p>By default, the <code>SELECT<\/code> statement returns rows in an unspecified order. When you add the <code>LIMIT<\/code> clause to the <code>SELECT<\/code> statement, the returned rows are unpredictable.<\/p>\n\n\n\n<p>Therefore, to ensure the <code>LIMIT<\/code> clause returns an expected output, you should always use it with an <code><a href=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-order-by\/\">ORDER BY<\/a><\/code> clause like this:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> \n    select_list\n<span class=\"hljs-keyword\">FROM<\/span> \n    table_name\n<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> \n    sort_expression\n<span class=\"hljs-keyword\">LIMIT<\/span> <span class=\"hljs-keyword\">offset<\/span>, <span class=\"hljs-keyword\">row_count<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-4\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The following picture illustrates the evaluation order of the <code>LIMIT<\/code> clause in the <code>SELECT<\/code> statement:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" src=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2024\/01\/mysql-limit-offset.svg\" alt=\"mysql limit offset\" class=\"wp-image-14583\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">MySQL LIMIT clause examples<\/h2>\n\n\n\n<p>We&#8217;ll use the <code>customers<\/code> table from the <a href=\"https:\/\/www.mysqltutorial.org\/getting-started-with-mysql\/mysql-sample-database\/\">sample database<\/a> for demonstration.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" src=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2023\/10\/customers.svg\" alt=\"\" class=\"wp-image-10765\"\/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">1) Using MySQL LIMIT to get the highest or lowest rows<\/h3>\n\n\n\n<p>This statement uses the <code>LIMIT<\/code> clause to get the top five customers who have the highest credit:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-5\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> \n    customerNumber, \n    customerName, \n    creditLimit\n<span class=\"hljs-keyword\">FROM<\/span>\n    customers\n<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> creditLimit <span class=\"hljs-keyword\">DESC<\/span>\n<span class=\"hljs-keyword\">LIMIT<\/span> <span class=\"hljs-number\">5<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-5\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a class=\"sql\" href=\"https:\/\/www.mysqltutorial.org\/tryit\/query\/mysql-limit\/#1\">Try It Out<\/a><\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"338\" height=\"130\" src=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2011\/03\/mysql-limit-get-highest-values1.png\" alt=\"mysql limit get highest values\" class=\"wp-image-3816\" title=\"mysql limit get highest values\" srcset=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2011\/03\/mysql-limit-get-highest-values1.png 338w, https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2011\/03\/mysql-limit-get-highest-values1-300x115.png 300w\" sizes=\"auto, (max-width: 338px) 100vw, 338px\" \/><\/figure>\n\n\n\n<p>In this example:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>First, the <code>ORDER BY<\/code> clause sorts the customers by credits from high to low.<\/li>\n\n\n\n<li>Then, the <code>LIMIT<\/code> clause returns the first 5 rows.<\/li>\n<\/ul>\n\n\n\n<p>Similarly, this example uses the <code>LIMIT<\/code> clause to find five customers who have the lowest credits:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-6\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> \n    customerNumber, \n    customerName, \n    creditLimit\n<span class=\"hljs-keyword\">FROM<\/span>\n    customers\n<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> creditLimit\n<span class=\"hljs-keyword\">LIMIT<\/span> <span class=\"hljs-number\">5<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-6\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a class=\"sql\" href=\"https:\/\/www.mysqltutorial.org\/tryit\/query\/mysql-limit\/#2\">Try It Out<\/a><\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"342\" height=\"135\" src=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2011\/03\/mysql-limit-get-lowest-values.png\" alt=\"mysql limit get lowest values\" class=\"wp-image-3814\" title=\"mysql limit get lowest values\" srcset=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2011\/03\/mysql-limit-get-lowest-values.png 342w, https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2011\/03\/mysql-limit-get-lowest-values-300x118.png 300w\" sizes=\"auto, (max-width: 342px) 100vw, 342px\" \/><\/figure>\n\n\n\n<p>In this example:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>First, the <code>ORDER BY<\/code> clause sorts the customers by credits from low to high.<\/li>\n\n\n\n<li>Then, the <code>LIMIT<\/code> clause returns the first 5 rows.<\/li>\n<\/ul>\n\n\n\n<p>Because there are more than 5 customers that have credits zero, the result of the query above may lead to an inconsistent result.<\/p>\n\n\n\n<p>To fix this issue, you need to add more columns to the <code>ORDER BY<\/code> clause to constrain the row in unique order:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-7\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> \n    customerNumber, \n    customerName, \n    creditLimit\n<span class=\"hljs-keyword\">FROM<\/span>\n    customers\n<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> \n    creditLimit, \n    customerNumber\n<span class=\"hljs-keyword\">LIMIT<\/span> <span class=\"hljs-number\">5<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-7\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a class=\"sql\" href=\"https:\/\/www.mysqltutorial.org\/tryit\/query\/mysql-limit\/#3\">Try It Out<\/a><\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"346\" height=\"117\" src=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/08\/MySQL-LIMIT-returns-N-rows.png\" alt=\"\" class=\"wp-image-8188\" srcset=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/08\/MySQL-LIMIT-returns-N-rows.png 346w, https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/08\/MySQL-LIMIT-returns-N-rows-200x68.png 200w\" sizes=\"auto, (max-width: 346px) 100vw, 346px\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">2) Using MySQL LIMIT clause for pagination<\/h3>\n\n\n\n<p>When you display data on the screen, you often want to divide rows into pages, where each page contains a limited number of rows like 10 or 20.<\/p>\n\n\n\n<p>To calculate the number of pages, you take the total rows divided by the number of rows per page.&nbsp;For fetching rows of a specific page, you can use the <code>LIMIT<\/code> clause.<\/p>\n\n\n\n<p>This query uses the <code>COUNT(*)<\/code> <a href=\"https:\/\/www.mysqltutorial.org\/mysql-aggregate-functions\/\">aggregate function<\/a> to get the total rows from the <code>customers<\/code> table:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-8\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> \n    <span class=\"hljs-keyword\">COUNT<\/span>(*) \n<span class=\"hljs-keyword\">FROM<\/span> \n    customers;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-8\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-9\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">+----------+\n| COUNT(*) |\n+----------+\n|      <span class=\"hljs-number\">122<\/span> |\n+----------+\n<span class=\"hljs-number\">1<\/span> row <span class=\"hljs-keyword\">in<\/span> <span class=\"hljs-keyword\">set<\/span> (0.00 sec)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-9\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Suppose that each page has 10 rows; to display 122 customers, you have 13 pages. The last 13th page contains two rows only.<\/p>\n\n\n\n<p>This query uses the <code>LIMIT<\/code> clause to get rows of page 1 which contains the first 10 customers sorted by the customer name:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-10\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> \n    customerNumber, \n    customerName\n<span class=\"hljs-keyword\">FROM<\/span>\n    customers\n<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> customerName    \n<span class=\"hljs-keyword\">LIMIT<\/span> <span class=\"hljs-number\">10<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-10\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a class=\"sql\" href=\"https:\/\/www.mysqltutorial.org\/tryit\/query\/mysql-limit\/#4\">Try It Out<\/a><\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"273\" height=\"217\" src=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/08\/MySQL-LIMIT-for-pagination-example.png\" alt=\"MySQL LIMIT for pagination example\" class=\"wp-image-8183\" srcset=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/08\/MySQL-LIMIT-for-pagination-example.png 273w, https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/08\/MySQL-LIMIT-for-pagination-example-200x159.png 200w\" sizes=\"auto, (max-width: 273px) 100vw, 273px\" \/><\/figure>\n\n\n\n<p>This query uses the <code>LIMIT<\/code> clause to get the rows of the second page that include rows 11 &#8211; 20:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-11\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> \n    customerNumber, \n    customerName\n<span class=\"hljs-keyword\">FROM<\/span>\n    customers\n<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> customerName    \n<span class=\"hljs-keyword\">LIMIT<\/span> <span class=\"hljs-number\">10<\/span>, <span class=\"hljs-number\">10<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-11\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a class=\"sql\" href=\"https:\/\/www.mysqltutorial.org\/tryit\/query\/mysql-limit\/#5\">Try It Out<\/a><\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"279\" height=\"227\" src=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/08\/MySQL-LIMIT-for-pagination-example-page-2.png\" alt=\"\" class=\"wp-image-8184\" srcset=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/08\/MySQL-LIMIT-for-pagination-example-page-2.png 279w, https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/08\/MySQL-LIMIT-for-pagination-example-page-2-200x163.png 200w\" sizes=\"auto, (max-width: 279px) 100vw, 279px\" \/><\/figure>\n\n\n\n<p>In this example, the clause <code>LIMIT 10, 10<\/code> returns 10 rows for the rows 11 &#8211; 20.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">3) Using MySQL LIMIT to get the n<sup>th<\/sup> highest or lowest value<\/h3>\n\n\n\n<p>To get the n<sup>th<\/sup> highest or lowest value, you use the following <code>LIMIT<\/code> clause:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-12\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> select_list\n<span class=\"hljs-keyword\">FROM<\/span> table_name\n<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> sort_expression\n<span class=\"hljs-keyword\">LIMIT<\/span> n<span class=\"hljs-number\">-1<\/span>, <span class=\"hljs-number\">1<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-12\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The clause <code>LIMIT n-1, 1<\/code> returns <code>1<\/code> row starting at the row <code>n<\/code>.<\/p>\n\n\n\n<p>For example, the following finds the customer who has the second-highest credit:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-13\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> \n    customerName, \n    creditLimit\n<span class=\"hljs-keyword\">FROM<\/span>\n    customers\n<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> \n    creditLimit <span class=\"hljs-keyword\">DESC<\/span>    \n<span class=\"hljs-keyword\">LIMIT<\/span> <span class=\"hljs-number\">1<\/span>,<span class=\"hljs-number\">1<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-13\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a class=\"sql\" href=\"https:\/\/www.mysqltutorial.org\/tryit\/query\/mysql-limit\/#6\">Try It Out<\/a><\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"252\" height=\"39\" src=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/08\/MySQL-LIMIT-find-nth-highest-row-example.png\" alt=\"MySQL LIMIT find nth highest row example\" class=\"wp-image-8185\" srcset=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/08\/MySQL-LIMIT-find-nth-highest-row-example.png 252w, https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/08\/MySQL-LIMIT-find-nth-highest-row-example-200x31.png 200w\" sizes=\"auto, (max-width: 252px) 100vw, 252px\" \/><\/figure>\n\n\n\n<p>Let&#8217;s double-check the result. This query returns all customers sorted by credits from high to low:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-14\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> \n    customerName, \n    creditLimit\n<span class=\"hljs-keyword\">FROM<\/span>\n    customers\n<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> \n    creditLimit <span class=\"hljs-keyword\">DESC<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-14\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a class=\"sql\" href=\"https:\/\/www.mysqltutorial.org\/tryit\/query\/mysql-limit\/#7\">Try It Out<\/a><\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"245\" height=\"237\" src=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/08\/MySQL-LIMIT-find-nth-highest-row-check-1.png\" alt=\"\" class=\"wp-image-8187\" srcset=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/08\/MySQL-LIMIT-find-nth-highest-row-check-1.png 245w, https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/08\/MySQL-LIMIT-find-nth-highest-row-check-1-200x193.png 200w\" sizes=\"auto, (max-width: 245px) 100vw, 245px\" \/><\/figure>\n\n\n\n<p>As you can see clearly from the output, the result was correct as expected.<\/p>\n\n\n\n<p class=\"note\">Note that this technique works when there are no two customers who have the same credit limits. To get a more accurate result, you should use the <code><a href=\"https:\/\/www.mysqltutorial.org\/mysql-window-functions\/mysql-dense_rank-function\/\">DENSE_RANK()<\/a><\/code> <a href=\"https:\/\/www.mysqltutorial.org\/mysql-window-functions\/\">window function<\/a>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">MySQL LIMIT &amp; DISTINCT clauses<\/h2>\n\n\n\n<p>If you use the <code>LIMIT<\/code> clause with the <code><a href=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-distinct\/\">DISTINCT<\/a><\/code>&nbsp;clause, MySQL immediately stops searching when it finds the number of unique rows specified in the <code>LIMIT<\/code> clause.<\/p>\n\n\n\n<p>The example uses the <code>LIMIT<\/code> clause with the <code>DISTINCT<\/code> clause to return the first five unique states in the <code>customers<\/code> table:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-15\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> <span class=\"hljs-keyword\">DISTINCT<\/span>\n    state\n<span class=\"hljs-keyword\">FROM<\/span>\n    customers\n<span class=\"hljs-keyword\">WHERE<\/span>\n    state <span class=\"hljs-keyword\">IS<\/span> <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>\n<span class=\"hljs-keyword\">LIMIT<\/span> <span class=\"hljs-number\">5<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-15\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a class=\"sql\" href=\"https:\/\/www.mysqltutorial.org\/tryit\/query\/mysql-distinct\/#10\" rel=\"noopener\">Try It Out<\/a><\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"80\" height=\"130\" src=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2011\/02\/MySQL-DISTINCT-with-LIMIT-clause.jpg\" alt=\"MySQL DISTINCT with LIMIT clause\" class=\"wp-image-4119\" title=\"MySQL DISTINCT with LIMIT clause\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Summary<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use the MySQL&nbsp;<code>LIMIT<\/code>&nbsp;clause to constrain the number of rows returned by the&nbsp;<code>SELECT<\/code> statement.<\/li>\n<\/ul>\n<div class=\"helpful-block-content\" data-title=\"\">\n\t<header>\n\t\t<div class=\"wth-question\">Was this tutorial helpful? <\/div>\n\t\t<div class=\"wth-thumbs\">\n\t\t\t<button\n\t\t\t\tdata-post=\"13736\"\n\t\t\t\tdata-post-url=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-limit\/\"\n\t\t\t\tdata-post-title=\"MySQL LIMIT\"\n\t\t\t\tdata-response=\"1\"\n\t\t\t\tclass=\"wth-btn-rounded wth-yes-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t\tclass=\"feather feather-thumbs-up block w-full h-full\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M14 9V5a3 3 0 0 0-3-3l-4 9v11h11.28a2 2 0 0 0 2-1.7l1.38-9a2 2 0 0 0-2-2.3zM7 22H4a2 2 0 0 1-2-2v-7a2 2 0 0 1 2-2h3\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t<button\n\t\t\t\tdata-response=\"0\"\n\t\t\t\tdata-post=\"13736\"\n\t\t\t\tdata-post-url=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-limit\/\"\n\t\t\t\tdata-post-title=\"MySQL LIMIT\"\n\t\t\t\tclass=\"wth-btn-rounded wth-no-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M10 15v4a3 3 0 0 0 3 3l4-9V2H5.72a2 2 0 0 0-2 1.7l-1.38 9a2 2 0 0 0 2 2.3zm7-13h2.67A2.31 2.31 0 0 1 22 4v7a2.31 2.31 0 0 1-2.33 2H17\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t<div class=\"wth-form hidden\">\n\t\t<div class=\"wth-form-wrapper\">\n\t\t\t<div class=\"wth-title\"><\/div>\n\t\t\t\n\t\t\t<textarea class=\"wth-message\"><\/textarea>\n\n\t\t\t<button class=\"btn btn-primary wth-btn-submit\">Send<\/button>\n\t\t\t<button class=\"btn wth-btn-cancel\">Cancel<\/button>\n\t\t\n\t\t<\/div>\n\t<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Summary: in this tutorial, you will learn how to use the MySQL LIMIT&nbsp;clause to constrain the number of rows returned by a query. Introduction to MySQL LIMIT clause The LIMIT clause is used in the SELECT statement to constrain the number of rows to return. The LIMIT clause accepts one or two arguments. The values [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"parent":174,"menu_order":11,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-13736","page","type-page","status-publish","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>MySQL LIMIT<\/title>\n<meta name=\"description\" content=\"In this tutorial, you will learn how to use MySQL LIMIT clause to constrain the number of rows returned by the SELECT statement.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-limit\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"MySQL LIMIT\" \/>\n<meta property=\"og:description\" content=\"In this tutorial, you will learn how to use MySQL LIMIT clause to constrain the number of rows returned by the SELECT statement.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-limit\/\" \/>\n<meta property=\"og:site_name\" content=\"MySQL Tutorial\" \/>\n<meta property=\"article:modified_time\" content=\"2024-01-20T02:07:33+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/08\/MySQL-LIMIT-illustration.png\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-basics\\\/mysql-limit\\\/\",\"url\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-basics\\\/mysql-limit\\\/\",\"name\":\"MySQL LIMIT\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-basics\\\/mysql-limit\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-basics\\\/mysql-limit\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.mysqltutorial.org\\\/wp-content\\\/uploads\\\/2019\\\/08\\\/MySQL-LIMIT-illustration.png\",\"datePublished\":\"2023-12-29T07:30:13+00:00\",\"dateModified\":\"2024-01-20T02:07:33+00:00\",\"description\":\"In this tutorial, you will learn how to use MySQL LIMIT clause to constrain the number of rows returned by the SELECT statement.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-basics\\\/mysql-limit\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-basics\\\/mysql-limit\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-basics\\\/mysql-limit\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.mysqltutorial.org\\\/wp-content\\\/uploads\\\/2019\\\/08\\\/MySQL-LIMIT-illustration.png\",\"contentUrl\":\"https:\\\/\\\/www.mysqltutorial.org\\\/wp-content\\\/uploads\\\/2019\\\/08\\\/MySQL-LIMIT-illustration.png\",\"width\":445,\"height\":240},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-basics\\\/mysql-limit\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.mysqltutorial.org\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"MySQL Basics\",\"item\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-basics\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"MySQL LIMIT\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/#website\",\"url\":\"https:\\\/\\\/www.mysqltutorial.org\\\/\",\"name\":\"MySQL Tutorial\",\"description\":\"A comprehensive MySQL Tutorial\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.mysqltutorial.org\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"MySQL LIMIT","description":"In this tutorial, you will learn how to use MySQL LIMIT clause to constrain the number of rows returned by the SELECT statement.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-limit\/","og_locale":"en_US","og_type":"article","og_title":"MySQL LIMIT","og_description":"In this tutorial, you will learn how to use MySQL LIMIT clause to constrain the number of rows returned by the SELECT statement.","og_url":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-limit\/","og_site_name":"MySQL Tutorial","article_modified_time":"2024-01-20T02:07:33+00:00","og_image":[{"url":"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/08\/MySQL-LIMIT-illustration.png","type":"","width":"","height":""}],"twitter_misc":{"Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-limit\/","url":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-limit\/","name":"MySQL LIMIT","isPartOf":{"@id":"https:\/\/www.mysqltutorial.org\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-limit\/#primaryimage"},"image":{"@id":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-limit\/#primaryimage"},"thumbnailUrl":"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/08\/MySQL-LIMIT-illustration.png","datePublished":"2023-12-29T07:30:13+00:00","dateModified":"2024-01-20T02:07:33+00:00","description":"In this tutorial, you will learn how to use MySQL LIMIT clause to constrain the number of rows returned by the SELECT statement.","breadcrumb":{"@id":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-limit\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-limit\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-limit\/#primaryimage","url":"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/08\/MySQL-LIMIT-illustration.png","contentUrl":"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/08\/MySQL-LIMIT-illustration.png","width":445,"height":240},{"@type":"BreadcrumbList","@id":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-limit\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.mysqltutorial.org\/"},{"@type":"ListItem","position":2,"name":"MySQL Basics","item":"https:\/\/www.mysqltutorial.org\/mysql-basics\/"},{"@type":"ListItem","position":3,"name":"MySQL LIMIT"}]},{"@type":"WebSite","@id":"https:\/\/www.mysqltutorial.org\/#website","url":"https:\/\/www.mysqltutorial.org\/","name":"MySQL Tutorial","description":"A comprehensive MySQL Tutorial","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.mysqltutorial.org\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/13736","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/comments?post=13736"}],"version-history":[{"count":3,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/13736\/revisions"}],"predecessor-version":[{"id":14584,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/13736\/revisions\/14584"}],"up":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/174"}],"wp:attachment":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/media?parent=13736"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}