MySQL : how to get last N records using query.

Question

Using offset X; will help to exclude first X recods but what to do if we dont know how many records are there in table.

Is there any query to know number of records?

Answer 1

First way:

SELECT * FROM tableName ORDER BY columnName DESC LIMIT 1;

Second way:

SET @variableName = (SELECT MAX(columnName) FROM tableName);
SELECT *FROM tableName WHERE columnName = @variableName;

Answer 2

Supposing that you mean last inserted records and every records has some key that correlates to time insertion (example classic id) you can solve very simply by ording in descendig order and limit the results.. Ex.

SELECT * FROM users ORDER BY id DESC LIMIT 10

where users table has a autoincrement primary key id

Answer 3

Sunil Shrestha beware that running the same query twice may return records in a different order unless you explicitly sort them. So be sure to include ORDER BY in your query. To get the bottom N records, sort in reverse order and take the top N by using LIMIT.

If you need the bottom N to be in a forward order, then use the above query as a subquery and apply ORDER BY again to sort the results of the subquery. SELECT * FROM (SELECT * FROM... ORDER BY ... LIMIT @N) ORDER BY ....

If you need to know the total number of records you can use the count() function: SELECT count(*) FROM mytable.

Answer 4

Brian KrOW JaScript Giannis thanks all

Answer 5

or is there any query for last N recods using limit

Answer 6

you can use the primary key