java - Getting last record from mysql

Question

I am using mysql and facing some problem. I want to retrieve last row that is inserted.

<< Below are details >>

Below is how I created table.

create table maxID (myID varchar(4))

I inserted four values in it as below

insert into maxID values ('A001')
insert into maxID values ('A002')
insert into maxID values ('A004')
insert into maxID values ('A003')

When I execute select myID, last_insert_id() as NewID from maxID, I get output as below

myId NewID
A001   0  
A002   0  
A004   0  
A003   0    

When I tried below code,

select myId, last_insert_id() as NewID, @rowid:=@rowid+1 as myrow from maxID, (SELECT @rowid:=0) as init

I get output as below.

myId NewID  rowid
A001   0      1
A002   0      2
A004   0      3
A003   0      4

However when I use code select myId, last_insert_id() as NewID, @rowid:=@rowid+1 as myrow from maxID, (SELECT @rowid:=0) as init where @rowid = 4, I get error as Uknown column 'myrow' in where clause

When I use where @rowid=4, I don't get any data in tables.

Link to play with data

Note: Here I am using 4 just to get desired output. Later I can get this from a query (select max(rowid) from maxID)

Please suggest me what need to do if I want to see only last record i.e. A003.

Thanks for your time.

Update:

I already have millions of data in my table so I can't add new column in it as suggested below.

Answer 1

Almost done. You succeed in getting the insert order. So:

select myId, @rowid:=@rowid+1 as myrow from maxID, (SELECT @rowid:=0) as init ORDER BY myrow desc LIMIT 1;

In my console I get the following:

mysql> select myId, @rowid:=@rowid+1 as myrow from maxID, (SELECT @rowid:=0) as
init ORDER BY myrow desc LIMIT 1;
+------+-------+
| myId | myrow |
+------+-------+
| A003 |     4 |
+------+-------+
1 row in set (0.00 sec)

Demo

UPDATE

Yak is right. My solution is not deterministic. Maybe it works for small amount of records. I found tons of post abount unreliability of default sorting of a SELECT statement (here for example). Next steps:

• Under which conditions the default SELECT sorting matches the insertion order?
• Is it possible to obtain the last inserted record in a table without an incremental id or an insertion timestamp?

I know it's not an answer, but stating the problem limit the problem.