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.

Nolde

Thanks for all comments... BUT is there any way to find LAST RECORD INSERTED in my case? – Nolde

Thousands of data doesn't mean much. Relational databases are built to handle millions of data. If you can't handle millions of data, you are consistently choosing to use the database wrong. – Vachel

Answer 1

It seems that a SELECT is not guaranteed to return rows in any specific order (without using an ORDER BY clause, of course).

As per the SQL-92 standard (p. 373):

If an < order by clause > is not specified, then the table specified by the < cursor specification > is T and the ordering of rows in T is implementation-dependent.

Okay, MySQL is not fully SQL-92-compliant, but this is a serious hint.

Laurynas Biveinis (apparently affiliated with Percona) also states:

The order of the rows in the absence of ORDER BY clause (...) could be different due to the moon phase and that is OK.

The MySQL manual says about InnoDB:

InnoDB always orders table rows according to [a PRIMARY KEY or NOT NULL UNIQUE index] if one is present.

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.

ALTER TABLE maxID ADD sequence INT DEFAULT NULL;
ALTER TABLE maxID ADD INDEX(sequence);
ALTER TABLE maxID MODIFY sequence INT AUTO_INCREMENT;

http://sqlfiddle.com/#!2/63a8d/1

Appellative

Answer 2

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.

Painting

OMG, I can't believe it. I don't know why I wanted to go with myrow=4 only. I didn't thought this solution... MANY THANKS :) :) – Nolde

to award bounty I need to wait 22 hours more :( I will give bounty points to you tomorrow... – Nolde

Please correct me if I am wrong, but I understand your strategy relies on the "default" order of rows returned by a SELECT. I believe a SELECT is not guaranteed to return rows in the same order they were inserted. MySQL "reserves the right" to perform optimisations, the most obvious example being if you add an index on your myID column. I haven't been able to find a definite answer to this question, but you may want to google for "mysql select row order guarantee" before relying 100% on this behaviour. - sqlfiddle.com/#!2/8e316/1/0 – Appellative

@YaK : Thanks for bringing this in notice. Is there any way to find last inserted id in my case? – Nolde

@adc : As YaK mentioned, I can't give bounty as my assumption was wrong. – Nolde

Answer 3

From your insert script, A004 is not the last inserted record. It's the third one. If you want to get the last record in alphabetical order (which A004 is), you must use

select myID from maxID order by myID desc limit 1

If you want the last inserted row, why don't you just use add an autoincrement column to your table? That's the point of those kinds of columns. The autoincrement column doesn't have to be the PK (it should be, but doesn't have to if you don't have the choice).

Sexpartite

sorry, its not giving what I want... NOTE: You are sorting it which is incorrect... by sorting records will be moved... – Nolde

Read it again, especially the last part: you need an autoincrement column, which may not be the PK. – Sexpartite

I know if I add autoincrement column I can do it... but as I already have table and thousand of data, I don't want to add extra column and increase database size.... In my scenario I want to find the last record inserted... – Nolde

Answer 4

As mentioned in the MySQL help for last_insert_id, you can only use it with **auto-increment columns. This means that you cannot make MySQL find the most recently inserted row for you, unless you know something about the order of the IDs. If they are sorted like your example suggests, then you can use

SELECT *
FROM maxID
WHERE myId = max(myId)

But I suggest adding an auto-increment column to the table and then use = last_insert_id() in your WHERE clause. See also this page for information on how to obtain the last ID.

Roundtheclock

please check my question again.. I am getting data as 0 there... I don't have ID as integer... – Nolde

I already have table and myId is varchar, so I can't alter my table now as it is linked to many other tables... :( – Nolde

I've updated the answer significantly when I realized that you don't have an auto-increment column. You should really add such a column if you want to track the last inserted row. – Roundtheclock

Answer 5

last_insert_id does not work because it only returns the last value generated by an auto-increment field. However I think that solution may be on the right track. I would suggest adding another column that is a auto-increment integer. You can then insert data and to retrieve the row you just inserted select the last_insert_id(). To retrieve the most recent row (inserted by any process) select the max number for the new column, or sort by it desc.

If you want to use the varchar column you can do an alphabetic sort, but that does not guarantee it will be the last row you inserted row or even the most recently inserted row.

The one other solution I can think of which may do what you need is to create a stored procedure which creates the row, inserts it into the table, and then returns it to you.

Your usage of @rowid is simply counting the order rows are returned to you. There is no guarantee that they are returned to you in the order of oldest to newest. There are a variety of things that can affect the order in which rows are stored.

Doelling

I added a bit to my answer. The big question though is do you need a record just entered or just the most recent one at any given point in time? Will this query immediately follow an insert or may it be done independently? – Doelling

I think you're out of luck given your current design. You could create another table that uses this as a foreign key with a DateTime or auto-increment ID column, since you say you cannot adjust the current schema. It's pretty hacky, but it would work. You could then set a trigger on either table to insert into the other one when it receives an insert, or manage the whole thing through a stored proc. – Doelling

ahh. thanks, I will try... BUT is there any other way to find last record in my scenario?? – Nolde

Unless there is an order to myID values and you can depend that they have always been inserted in that order, then no there is not. – Doelling

In my question, I am creating rowid as column... Using that, will the problem will be solved? (Look for myId NewID rowid) – Nolde

No. As I explained above that is simple counting the rows and the rows will not necessarily be returned in the order they were inserted into the database. – Doelling

ok... thanks for help... I will check for new table and adding auto_increment column... – Nolde

Answer 6

Please use the following query.

Select * from maxID order by myId desc limit 1,1;

Petticoat