How to select last 10 rows from MySQL?

To select last 10 rows from MySQL, we can use a subquery with SELECT statement and Limit concept. The following is an example.

Creating a table.

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> create table </span><span class="typ">Last10RecordsDemo</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"> id </span><span class="kwd">int</span><span class="pun">,</span><span class="pln">
   </span><span class="pun">-></span><span class="pln"> name varchar</span><span class="pun">(</span><span class="lit">100</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.75</span><span class="pln"> sec</span><span class="pun">)</span>

Inserting records into the table.

<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">Last10RecordsDemo</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="str">'John'</span><span class="pun">),(</span><span class="lit">2</span><span class="pun">,</span><span class="str">'Carol'</span><span class="pun">),(</span><span class="lit">3</span><span class="pun">,</span><span class="str">'Bob'</span><span class="pun">),(</span><span class="lit">4</span><span class="pun">,</span><span class="str">'Sam'</span><span class="pun">),(</span><span class="lit">5</span><span class="pun">,</span><span class="str">'David'</span><span class="pun">),(</span><span class="lit">6</span><span class="pun">,</span><span class="str">'Taylor'</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">6</span><span class="pln"> rows affected </span><span class="pun">(</span><span class="lit">0.12</span><span class="pln"> sec</span><span class="pun">)</span><span class="pln">
</span><span class="typ">Records</span><span class="pun">:</span><span class="pln"> </span><span class="lit">6</span><span class="pln">  </span><span class="typ">Duplicates</span><span class="pun">:</span><span class="pln"> </span><span class="lit">0</span><span class="pln">  </span><span class="typ">Warnings</span><span class="pun">:</span><span class="pln"> </span><span class="lit">0</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">Last10RecordsDemo</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">7</span><span class="pun">,</span><span class="str">'Sam'</span><span class="pun">),(</span><span class="lit">8</span><span class="pun">,</span><span class="str">'Justin'</span><span class="pun">),(</span><span class="lit">9</span><span class="pun">,</span><span class="str">'Ramit'</span><span class="pun">),(</span><span class="lit">10</span><span class="pun">,</span><span class="str">'Smith'</span><span class="pun">),(</span><span class="lit">11</span><span class="pun">,</span><span class="str">'Clark'</span><span class="pun">),(</span><span class="lit">12</span><span class="pun">,</span><span class="str">'Johnson'</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">6</span><span class="pln"> rows affected </span><span class="pun">(</span><span class="lit">0.14</span><span class="pln"> sec</span><span class="pun">)</span><span class="pln">
</span><span class="typ">Records</span><span class="pun">:</span><span class="pln"> </span><span class="lit">6</span><span class="pln">  </span><span class="typ">Duplicates</span><span class="pun">:</span><span class="pln"> </span><span class="lit">0</span><span class="pln">  </span><span class="typ">Warnings</span><span class="pun">:</span><span class="pln"> </span><span class="lit">0</span>

To display all records.

<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">Last10RecordsDemo</span><span class="pun">;</span>

The following is the output.

+------+---------+
| id   | name    |
+------+---------+
|    1 | John    |
|    2 | Carol   |
|    3 | Bob     |
|    4 | Sam     |
|    5 | David   |
|    6 | Taylor  |
|    7 | Sam     |
|    8 | Justin  |
|    9 | Ramit   |
|   10 | Smith   |
|   11 | Clark   |
|   12 | Johnson |
+------+---------+
12 rows in set (0.00 sec)

The following is the syntax to get the last 10 records from the table. Here, we have used LIMIT clause.

<span class="pln">SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="pun">(</span><span class="pln">
   SELECT </span><span class="pun">*</span><span class="pln"> FROM yourTableName ORDER BY id DESC LIMIT </span><span class="lit">10</span><span class="pln">
</span><span class="pun">)</span><span class="typ">Var1</span><span class="pln">
   ORDER BY id ASC</span><span class="pun">;</span>

Let us now implement the above query.

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="pun">(</span><span class="pln">
   </span><span class="pun">-></span><span class="pln">     SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Last10RecordsDemo</span><span class="pln"> ORDER BY id DESC LIMIT </span><span class="lit">10</span><span class="pln">
   </span><span class="pun">-></span><span class="pln"> </span><span class="pun">)</span><span class="typ">Var1</span><span class="pln">
   </span><span class="pun">-></span><span class="pln">
   </span><span class="pun">-></span><span class="pln"> ORDER BY id ASC</span><span class="pun">;</span>

The following is the output that displays the last 10 records.

+------+---------+
| id   | name    |
+------+---------+
|    3 | Bob     |
|    4 | Sam     |
|    5 | David   |
|    6 | Taylor  |
|    7 | Sam     |
|    8 | Justin  |
|    9 | Ramit   |
|   10 | Smith   |
|   11 | Clark   |
|   12 | Johnson |
+------+---------+
10 rows in set (0.00 sec)

We can match both records with the help of the SELECT statement.

Total Records (12)

Last 10 Records (10)