CODEWITHSUNDEEP
mysqlsql
Federico Pessina
Federico Pessina

Reputation: 209

MySQL select the last two inserted IDs

How can I select the last two inserted IDs?

Here I explain how I could use these. This is my query:

INSERT INTO Table1 (FIELD1, FIELD2, FK_TABLE2, FIELD6) 
(SELECT FIELD1, FIELD2
,(SELECT MAX(PK_Table2)
    FROM Table2
    )
,(FIELD4 + FIELD5) FROM Table1 WHERE FIELD3 = (
    (SELECT MAX(PK_Table2)
        FROM Table2) - 1
    ))

This should almost duplicate all records linked to the last but one record of table2, for the last record of table2.

This works right now, but only because I had not deleted a record yet.

If I delete the last record from table2 and insert another one, my generator will generate a new PK that is not the last+1.

eg: Last two IDs are: 18-19. I Delete 19 and insert another one. Now the last two IDs are 18-20.
So Max(PK_Table2) will be 20 and Max(PK_Table2)-1 will be 19, but I need it to be 18.

Upvotes: 0

Views: 62

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 95053

You need to steps:

  1. Get the max ID
  2. Get the max ID less then that

This would be:

WHERE FIELD3 = 
(
  SELECT MAX(PK_Table2) FROM Table2
  WHERE PK_Table2 < (SELECT MAX(PK_Table2) FROM Table2)
)

Another approch: Get the last two, then get the second last one.

WHERE FIELD3 = 
(
  SELECT PK_Table2
  FROM (SELECT PK_Table2 FROM Table2 ORDER BY PK_Table2 DESC LIMIT 2) x
  ORDER BY PK_Table2 LIMIT 1
)

Upvotes: 2

Related Questions