Select last N rows from MySQL

Question

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 ?

Answer 1

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.

Answer 2

SELECT * FROM table ORDER BY id DESC LIMIT 50

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

Answer 3

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!!!!

Answer 4

Use it to retrieve last n rows from mysql

Select * from tbl order by id desc limit 10;

use limit according to N value.