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.
It seems that a
SELECT
is not guaranteed to return rows in any specific order (without using anORDER BY
clause, of course).As per the SQL-92 standard (p. 373):
Okay, MySQL is not fully SQL-92-compliant, but this is a serious hint.
Laurynas Biveinis (apparently affiliated with Percona) also states:
The MySQL manual says about InnoDB:
As far as I am concerned, I assume MySQL could also reorder rows after an
OPTIMIZE TABLE
or even reuse empty spaces after many deletes and inserts (I have tried to find an example of this, and have failed so far).Given your table structure, the bottomline is, unfortunately, that so many factors could have altered the order of the rows; I see no solution to reliably determine the order they were inserted. Unless you kept all binary logs since you created the table, of course ;)
Nevertheless, you may still want to add a
sequence
column to your table. Existing rows would be assigned a possibly inaccurate sequence number, but at least future rows will be correctly sequenced.http://sqlfiddle.com/#!2/63a8d/1