Select last N rows from MySQL

MysqlDatabase

Mysql Problem Overview

I want to select last 50 rows from MySQL database within column named id which is primary key. Goal is that the rows should be sorted by id in ASC order, that’s why this query isn’t working

SELECT 
    *
FROM
    `table`
ORDER BY id DESC
LIMIT 50;

Also it’s remarkable that rows could be manipulated (deleted) and that’s why following query isn’t working either

SELECT 
    *
FROM
    `table`
WHERE
    id > ((SELECT 
            MAX(id)
        FROM
            chat) - 50)
ORDER BY id ASC;

Question: How is it possible to retrieve last N rows from MySQL database that can be manipulated and be in ASC order ?

Mysql Solutions

Solution 1 - Mysql

You can do it with a sub-query:

SELECT * FROM (
    SELECT * FROM table ORDER BY id DESC LIMIT 50
) sub
ORDER BY id ASC

This will select the last 50 rows from table, and then order them in ascending order.

Solution 2 - Mysql

SELECT * FROM table ORDER BY id DESC LIMIT 50

save resources make one query, there is no need to make nested queries

Solution 3 - Mysql

SELECT * FROM table ORDER BY id DESC,datechat desc LIMIT 50

If you have a date field that is storing the date(and time) on which the chat was sent or any field that is filled with incrementally(order by DESC) or desinscrementally( order by ASC) data per row put it as second column on which the data should be order.

That's what worked for me!!!! hope it will help!!!!

Solution 4 - Mysql

Use it to retrieve last n rows from mysql

Select * from tbl order by id desc limit 10;

use limit according to N value.

Solution 5 - Mysql

select * from Table ORDER BY id LIMIT 30

Notes:

  • id should be unique.
  • You can control the numbers of rows returned by replacing the 30 in the query
Categories
Recommended Mysql Solutions
Recommended Database Solutions

Previous Solution:

Local (?) variable referenced before assignment

PythonPython 3.x

Next Solution:

Adding onClick event dynamically using jQuery

JavascriptJqueryHtml

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionnanobashView Question on Stackoverflow
Solution 1 - MysqlnewfurnitureyView Answer on Stackoverflow
Solution 2 - MysqlBuzzView Answer on Stackoverflow
Solution 3 - MysqlaidonsnousView Answer on Stackoverflow
Solution 4 - MysqlRahul ChaudharyView Answer on Stackoverflow
Solution 5 - MysqlDarshanView Answer on Stackoverflow