Getting last record from mysql

by

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.

More Related Contents:

  1. How do I get a result across 2 tables
  2. Hours and seconds are lost when storing a date in the database [closed]
  3. Multiple queries executed in java in single statement
  4. MySQL and JDBC with rewriteBatchedStatements=true
  5. “EEE MMM dd HH:mm:ss ZZZ yyyy” date format to java.sql.Date
  6. right syntax to use near ‘?’
  7. Running a .sql script using MySQL with JDBC
  8. Prevent SQL injection attacks in a Java program
  9. LINQ for Java tool [closed]
  10. java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/dbname [duplicate]
  11. How to use prepared statement for select query in Java?
  12. A datetime equivalent in java.sql ? (is there a java.sql.datetime ?)
  13. Hibernate Criteria API: get n random rows
  14. Using threads to make database requests
  15. How to convert java.util.Date to java.sql.Date?
  16. Efficient way to do batch INSERTS with JDBC
  17. SQLite in Android How to update a specific row
  18. com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:Communications link failure [duplicate]
  19. Connect to remote MySQL database through SSH using Java
  20. The most sophisticated way for creating comma-separated Strings from a Collection/Array/List?
  21. JDBC ResultSet get columns with table alias
  22. Getting an exception ORA-00942: table or view does not exist – when inserting into an existing table
  23. ‘0000-00-00 00:00:00’ can not be represented as java.sql.Timestamp error
  24. SimpleDateFormatter.parse giving output in different format than specified
  25. JPA Native Query select and cast object
  26. ResultSet: Exception: set type is TYPE_FORWARD_ONLY — why?
  27. Table ‘DBNAME.hibernate_sequence’ doesn’t exist
  28. How to setup Hibernate to read/write to different datasources?
  29. JDBC MySQL connection using Unix Socket
  30. Searching between dates in SQL with JDBC?

Leave a Comment