5

There are many solutions in stackoverflow itself where the objective was to read the last n rows of the table using either an auto-increment field or timestamp: for example, the following query fetches the last ten records from a table named tab in the descending order of the field values named id which is an auto increment field in the table:

Select * from tab order by id desc limit 10

My question is: Is there any alternative way without having to get an auto increment field or timestamp to accomplish the task to get the same output?

Tips: The motivation to ask this question comes from the fact that: as we store records into tables and when query the database with a simple query without specifying any criteria like :

Select * from tab

Then the order of the output is same as the order of the records as inserted into the table. So is there any way to get the records in the reverse order of what they were entered into the database?

Improve this question
4
  • 1
    The order is the same only by coincidence. It cannot be guaranteed and an index on a string column, say, will undermine that order Commented Jan 8, 2016 at 7:55
  • Couldn't get it, an index on a string column or any column will undermine the order? @Strawberry Commented Apr 21, 2016 at 8:22
  • See answer below, by way of illustration... Commented Apr 21, 2016 at 8:40
  • thank you for the response, my 1 vote there @Strawberry Commented Apr 22, 2016 at 15:11

2 Answers 2

Reset to default
6

Data in mysql is not ordered- you don't have any guarantee on the order of the records you'll get unless you'll specify order by in your query.

So no, unless you'll order by timestamp, id, or any other field, you can't get the last rows, simply because there's no 'last' without the order

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

thank you. Is it because data are actually stored in tress in database? I may be wrong
@rosemary:- Yes, database use B+ Trees to store the data internally into the database.
4

In the SQL world, order is not an inherent property of a set of data. Thus, you get no guarantees from your RDBMS that your data will come back in a certain order -- or even in a consistent order -- unless you query your data with an ORDER BY clause.

So if you don't have the data sorted by some id or some column then you cannot track the data based on its sorting. So it is not guaranteed how MYSQL will store the data and hence you cannot get the last n records.

You can also check this article:

Caveats

Ordering of Rows

In the absence of ORDER BY, records may be returned in a different order than the previous MEMORY implementation.

This is not a bug. Any application relying on a specific order without an ORDER BY clause may deliver unexpected results. A specific order without ORDER BY is a side effect of a storage engine and query optimizer implementation which may and will change between minor MySQL releases.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.