How can I select the row with the highest ID in MySQL?
MysqlMysql Problem Overview
How can I select the row with the highest ID in MySQL? This is my current code:
SELECT * FROM permlog WHERE max(id)
Errors come up, can someone help me?
Mysql Solutions
Solution 1 - Mysql
SELECT * FROM permlog ORDER BY id DESC LIMIT 0, 1
Solution 2 - Mysql
if it's just the highest ID you want. and ID is unique/auto_increment:
SELECT MAX(ID) FROM tablename
Solution 3 - Mysql
For MySQL:
SELECT *
FROM permlog
ORDER BY id DESC
LIMIT 1
You want to sort the rows from highest to lowest id, hence the ORDER BY id DESC. Then you just want the first one so LIMIT 1:
> The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement.
> [...]
> With one argument, the value specifies the number of rows to return from the beginning of the result set
Solution 4 - Mysql
SELECT *
FROM permlog
WHERE id = ( SELECT MAX(id) FROM permlog ) ;
This would return all rows with highest id, in case id column is not constrained to be unique.
Solution 5 - Mysql
SELECT MAX(id) FROM TABLENAME
This identifies the largest id and returns the value
Solution 6 - Mysql
SELECT MAX(ID) FROM tablename LIMIT 1
Use this query to find the highest ID in the MySQL table.
Solution 7 - Mysql
Suppose you have mulitple record for same date or leave_type but different id and you want the maximum no of id for same date or leave_type as i also sucked with this issue, so Yes you can do it with the following query:
select * from tabel_name where employee_no='123' and id=(
select max(id) from table_name where employee_no='123' and leave_type='5'
)
Solution 8 - Mysql
This is the only proposed method who actually selects the whole row, not only the max(id) field. It uses a subquery
SELECT * FROM permlog WHERE id = ( SELECT MAX( id ) FROM permlog )
Solution 9 - Mysql
SELECT * FROM `permlog` as one
RIGHT JOIN (SELECT MAX(id) as max_id FROM `permlog`) as two
ON one.id = two.max_id
Solution 10 - Mysql
Since both SELECT MAX(id) FROM table and SELECT id FROM table ORDER BY id DESC LIMIT 0,1 fulfill the goal, the interesting part is, which performs better.
SELECT MAX(id) FROM table: 152ms
SELECT id FROM table ORDER BY id DESC LIMIT 0,1: 25ms
(InnoDB-table with 55M rows on MySQL 8.0, 10 runs, average result)
Of course thats not representive, but gives an idea, that the ORDER BY method performs significantly better.