How can I SELECT the last row in a MySQL table ? I don't have any auto increment key

Question

I want to SELECT the last row in a MySQL table. I don't have any auto increment key in that table.

public String getLastLeaveId() {
    try {
        Statement stmt  = getConnection();
        String sql="select leaveID from applied leave"; // here I want some logic that will fetch only leave_id from last row only
        stmt.execute(sql);
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return null;
}

//Table Structure --->
(leave_id varchar(50) primary key,emp_id varchar(40),birthday varchar(20),anniv varchar(20),rh varchar(200),total int,foriegn key(emp_id) reference employee);

I am just writing a String sql query to be executed by statement and fetching the ResultSet. — Arpit, Dec 24 '13 at 12:38
possible duplicate of [How to select the last record from MySQL table using SQL syntax](http://stackoverflow.com/questions/2659253/how-to-select-the-last-record-from-mysql-table-using-sql-syntax) — AJ., Dec 24 '13 at 12:39
Not possible... not on MyISAM or InnoDB engine. The storage engine cant give an guarantee that the records are returnd in the same order. — Raymond Nijland, Dec 24 '13 at 14:10

score 2 · Answer 1 · answered Dec 24 '13 at 12:34

2

Order descending on whatever column you're ordering now and do a limit 0,1 at the end.

answered Dec 24 '13 at 12:34

Toon Borgers

3,638
1
14
22

score 0 · Answer 2 · answered Dec 24 '13 at 12:39

There is no implicit ordering in a database table. If you don't have an auto-increment column, a date/time column, or some other column that follows a natural order when you insert rows, you're limited to using some other way of faking it.

You don't show a table structure in your question, nor do you state whether you're using MyISAM or InnoDB. MyISAM stores rows in order they're inserted (usually) so you can use something inneficient like ResultSet.last() from Java to get the last row, but if you're not using MyISAM you can't depend on that. InnoDB, the default storage engine, stores records in a more efficient B-Tree order, and does not retain the insert order at all, but rather stores records according to the primary key's order.

In short, you'll need to pick a field that has some natural order (or invent one) and order by that.

score 0 · Answer 3 · answered Dec 24 '13 at 12:55

Without having either a where clause and/or order by condition you can't fetch the last row in a query directly. You need to traverse through all the rows to stop before last row.

Only positive options I see are:

Select all rows. execute a rs.last() and then read it.
Use order by desc clause based on a column.
Define a virtual row_num on all selected rows and run a select with desc order on that row_num.
Execute two statements. One to find row count and the second to limit to fetch last row.

Pseudo example on 4th option above:

int rowCount = result from "Select count(*) from my_table";
String query = "select * from my_table limit " + ( rowCount - 1 ) + ", 1";
rs = stmt.execute above query.
read the only record, none but the last record, from rs, fetched.

score -1 · Answer 4 · answered Dec 24 '13 at 12:48

-1

it's may be useful

   SELECT MAX(ID) FROM YourTable

   // Similarly, ORDER BY with LIMIT:

    SELECT ID FROM YourTable ORDER BY ID Desc Limit 1

answered Dec 24 '13 at 12:48

Priya jain

753
2
5
15

score -2 · Answer 5 · edited Dec 24 '13 at 12:54

-2

This is what you want.

SELECT * FROM table_name ORDER BY id DESC LIMIT 1

edited Dec 24 '13 at 12:54

shadyyx

15,825
6
60
95

answered Dec 24 '13 at 12:38

Prashant Shilimkar

8,402
13
54
89

How can I SELECT the last row in a MySQL table ? I don't have any auto increment key

5 Answers5