mysql: viewing table last records

[sigix]

how to select mysql db table's last records
like if I have records
1
2
3
4
5
want to select last 2 records i.e 4,5

[sarumont]

Do you have the records numbered?

select * from TABLE where number_field>=count(TABLE)-1;

Replace TABLE with the table name and number_field with the field that has the record numbers in them. I'm not sure exactly how to do this if you don't have a numbered field.

[sigix]

perhaps it is not possible to grab records from bottom without some id

[jasonlambert]

perhaps it is not possible to grab records from bottom without some id

Correct.

[Dolda2000]

No, that's wrong - you can use the LIMIT condition in the SELECT clause. LIMIT fetches given number of rows from the start of the returned set, so obviously you'll need to reverse the sorting order. Something like this:

SELECT * FROM table ORDER BY id DESC LIMIT 2;

[sarumont]

But without an id column, you would have to find column to sort them by so as to get the ones you wanted on the bottom...depending on what you are using this for.

[Dolda2000]

Well, I guess I'm just assuming that everyone always sort their results. =)

Well, that's not really it, though - I just thought that if you want the last results, you probably have an ordering anyway so that "last" has a defined meaning.

[linuxadvocate]

Keep in mind that in MySQL the rows have to inherent order (although they may appear to, don't trust it!).

So consider the real-world problem you want to solve. Do you want the last one inserted? This deals with time, and the best way to handle this is to have a timestamp on each record, sort the results by time DESCending, and LIMIT 1. You could also just get the max value for the column and then grab the record directly.

I recommend you don't use ID to find the latest record, unless you handle the ID yourself (meaning NOT using autoincrement). The reason is that while the current behavior of autoincrement is MAX()+1, this behavior is NOT guaranteed to remain the same (in fact, the behavior changed not too long ago). What if future autoinc columns reuse deleted IDs?

-Ian

[gigo6000]

If you don't have an id or date column to sort by, (in my case the date column had incorrect values) you could try using LIMIT:


Suppose we have a total 1000 rows in the table

select count(*) from table;
+----------+
| count(*) |
+----------+
| 1000 |
+----------+

then,

select * from table limit 950,1000

would give us the last 50 rows

[devils casper]

Hi and Welcome gigo6000 !

Its a very old thread. I would suggest you to post in a bit recent threads.