How to select first 10 elements from a MySQL database?

To select first 10 elements from a database using SQL ORDER BY clause with LIMIT 10.

The syntax is as follows

SELECT *FROM yourTableName ORDER BY yourIdColumnName LIMIT 10;

To understand the above syntax, let us create a table. The query to create a table is as follows

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> create table </span><span class="typ">Clients</span><span class="pln">
   </span><span class="pun">-</span><span class="pln"> </span><span class="pun">></span><span class="pln"> </span><span class="pun">(</span><span class="pln">
   </span><span class="pun">-</span><span class="pln"> </span><span class="pun">></span><span class="pln"> </span><span class="typ">Client_Id</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> NOT NULL AUTO_INCREMENT PRIMARY KEY</span><span class="pun">,</span><span class="pln">
   </span><span class="pun">-</span><span class="pln"> </span><span class="pun">></span><span class="pln"> </span><span class="typ">ClientName</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">20</span><span class="pun">)</span><span class="pln">
   </span><span class="pun">-</span><span class="pln"> </span><span class="pun">></span><span class="pln"> </span><span class="pun">);</span><span class="pln">
</span><span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">0</span><span class="pln"> rows affected </span><span class="pun">(</span><span class="lit">0.51</span><span class="pln"> sec</span><span class="pun">)</span>

Insert some records in the table using INSERT command.

The query is as follows

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Clients</span><span class="pun">(</span><span class="typ">ClientName</span><span class="pun">)</span><span class="pln"> values</span><span class="pun">(</span><span class="str">'Larry'</span><span class="pun">);</span><span class="pln">
</span><span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.09</span><span class="pln"> sec</span><span class="pun">)</span><span class="pln">
mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Clients</span><span class="pun">(</span><span class="typ">ClientName</span><span class="pun">)</span><span class="pln"> values</span><span class="pun">(</span><span class="str">'Sam'</span><span class="pun">);</span><span class="pln">
</span><span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.19</span><span class="pln"> sec</span><span class="pun">)</span><span class="pln">
mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Clients</span><span class="pun">(</span><span class="typ">ClientName</span><span class="pun">)</span><span class="pln"> values</span><span class="pun">(</span><span class="str">'Bob'</span><span class="pun">);</span><span class="pln">
</span><span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.18</span><span class="pln"> sec</span><span class="pun">)</span><span class="pln">
mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Clients</span><span class="pun">(</span><span class="typ">ClientName</span><span class="pun">)</span><span class="pln"> values</span><span class="pun">(</span><span class="str">'David'</span><span class="pun">);</span><span class="pln">
</span><span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.15</span><span class="pln"> sec</span><span class="pun">)</span><span class="pln">
mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Clients</span><span class="pun">(</span><span class="typ">ClientName</span><span class="pun">)</span><span class="pln"> values</span><span class="pun">(</span><span class="str">'John'</span><span class="pun">);</span><span class="pln">
</span><span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.17</span><span class="pln"> sec</span><span class="pun">)</span><span class="pln">
mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Clients</span><span class="pun">(</span><span class="typ">ClientName</span><span class="pun">)</span><span class="pln"> values</span><span class="pun">(</span><span class="str">'James'</span><span class="pun">);</span><span class="pln">
</span><span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.14</span><span class="pln"> sec</span><span class="pun">)</span><span class="pln">
mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Clients</span><span class="pun">(</span><span class="typ">ClientName</span><span class="pun">)</span><span class="pln"> values</span><span class="pun">(</span><span class="str">'Robert'</span><span class="pun">);</span><span class="pln">
</span><span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.11</span><span class="pln"> sec</span><span class="pun">)</span><span class="pln">
mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Clients</span><span class="pun">(</span><span class="typ">ClientName</span><span class="pun">)</span><span class="pln"> values</span><span class="pun">(</span><span class="str">'Carol'</span><span class="pun">);</span><span class="pln">
</span><span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.15</span><span class="pln"> sec</span><span class="pun">)</span><span class="pln">
mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Clients</span><span class="pun">(</span><span class="typ">ClientName</span><span class="pun">)</span><span class="pln"> values</span><span class="pun">(</span><span class="str">'Mike'</span><span class="pun">);</span><span class="pln">
</span><span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.12</span><span class="pln"> sec</span><span class="pun">)</span><span class="pln">
mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Clients</span><span class="pun">(</span><span class="typ">ClientName</span><span class="pun">)</span><span class="pln"> values</span><span class="pun">(</span><span class="str">'Maxwell'</span><span class="pun">);</span><span class="pln">
</span><span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.27</span><span class="pln"> sec</span><span class="pun">)</span><span class="pln">
mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Clients</span><span class="pun">(</span><span class="typ">ClientName</span><span class="pun">)</span><span class="pln"> values</span><span class="pun">(</span><span class="str">'Chris'</span><span class="pun">);</span><span class="pln">
</span><span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.23</span><span class="pln"> sec</span><span class="pun">)</span><span class="pln">
mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Clients</span><span class="pun">(</span><span class="typ">ClientName</span><span class="pun">)</span><span class="pln"> values</span><span class="pun">(</span><span class="str">'Ramit'</span><span class="pun">);</span><span class="pln">
</span><span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.18</span><span class="pln"> sec</span><span class="pun">)</span>

Display all records from the table using SELECT statement.

The query is as follows

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="kwd">from</span><span class="pln"> </span><span class="typ">Clients</span><span class="pun">;</span>

The following is the output

+-----------+------------+
| Client_Id | ClientName |
+-----------+------------+
|         1 | Larry      |
|         2 | Sam        |
|         3 | Bob        |
|         4 | David      |
|         5 | John       |
|         6 | James      |
|         7 | Robert     |
|         8 | Carol      |
|         9 | Mike       |
|        10 | Maxwell    |
|        11 | Chris      |
|        12 | Ramit      |
+-----------+------------+
12 rows in set (0.00 sec)

Here is the query to select first 10 elements

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="kwd">from</span><span class="pln"> </span><span class="typ">Clients</span><span class="pln"> ORDER BY </span><span class="typ">Client_Id</span><span class="pln"> LIMIT </span><span class="lit">10</span><span class="pun">;</span>

The following is the output

+-----------+------------+
| Client_Id | ClientName |
+-----------+------------+
|         1 | Larry      |
|         2 | Sam        |
|         3 | Bob        |
|         4 | David      |
|         5 | John       |
|         6 | James      |
|         7 | Robert     |
|         8 | Carol      |
|         9 | Mike       |
|        10 | Maxwell    |
+-----------+------------+
10 rows in set (0.00 sec)

Here is the alternate query to select first 10 elements.

The query is as follows

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="kwd">from</span><span class="pln"> </span><span class="typ">Clients</span><span class="pln"> limit </span><span class="lit">0</span><span class="pun">,</span><span class="lit">10</span><span class="pun">;</span>

The following is the output

+-----------+------------+
| Client_Id | ClientName |
+-----------+------------+
|         1 | Larry      | 
|         2 | Sam        |
|         3 | Bob        |
|         4 | David      |
|         5 | John       |
|         6 | James      |
|         7 | Robert     |
|         8 | Carol      |
|         9 | Mike       |
|        10 | Maxwell    |
+-----------+------------+
10 rows in set (0.00 sec)