how to get last row value in mysql database

Question

I have a MySQL table with following data (sample);

UserId  -   DeviceId    -   StartDate   -   EndDate
------------------------------------------------------
1001        D119822         11/27/2011      12/02/2011
1001        D198726         11/27/2011      12/02/2011
1001        D552833         11/27/2011      12/02/2011
1001        D980993         11/27/2011      12/02/2011

I have searched for some kind of row_num or ordinal_position thing, but found nothing. How could I get the latest record from the above table without adding a serial/auto increment number column?

Answer 1

Try this query, i have tested it and it works for me.

select s.*, @rownum:=@rownum+1 as rank
from your_table s, (SELECT @rownum:=0) r
order by rank DESC
LIMIT 1;

Answer 2

"last" can be alphabetical, time, value etc. You need to define that in a simply query:

SELECT *
FROM Mytable
ORDER BY (some criteria that defines "last") DESC
LIMIT 1;

Answer 3

Ok, so the answer to this question is simply NO. MySQL does not provide you with a built in feature that let you keep track of the order in which data has been inserted.

You could add a column that auto increments (unique id) when a row is inserted, or you could add a time stamp column to keep track of the time at which the row was inserted.

If you're not allowed to manipulate the table then maybe you could add a new table to store the latest added row of data (but this is just a bad solution really).