select last inserted row

greedyisg00d

how can I select the last 2 or 3 inserted row in the table? thanks

johanafm

Assuming you have a recent MySQL version (changed somehwere in 5.1.XX iirc) and also insert your data with one query, that is[coee]
VALUES (...),(...)[/code]
rather than separate queries

INSERT INTO ... VALUES(...);
INSERT INTO ... VALUES(...);

mysqli::insert_id holds the FIRST autogenerated value for the last statement. If you're not using PHP, select last_insert_id() MAY do the same thing. All I remember is that there are, or used to be, differences between those two. Refer the reference manual to find out for sure.

Then select anything where id > ...

NINTHTJ

Another good and easy way to do it, may be to do the following:

SELECT * FROM `table` ORDER BY id DESC LIMIT 3

As the ID is auto-incrementing ( hopefully you have that set ), it will order all the results by the table starting at the highest, which will therefore be the last inserted. We simply then put a limit on there to define how many results we want.

laserlight

NINTHTJ wrote:
As the ID is auto-incrementing ( hopefully you have that set ), it will order all the results by the table starting at the highest, which will therefore be the last inserted. We simply then put a limit on there to define how many results we want.

Considering that greedyisg00d wants to "select the last 2 or 3 inserted row in the table" rather than the row that was just inserted by the previous statement run in the script, this sounds correct, otherwise the susceptibility to a race condition would not make it ideal.

If the id is not auto incrementing, or if the auto increment is not guaranteed to be monotonically increasing, then consider recording a timestamp of the insertion.