'How can I select the row with the highest ID in MySQL?
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?
Solution 1:[1]
SELECT * FROM permlog ORDER BY id DESC LIMIT 0, 1
Solution 2:[2]
if it's just the highest ID you want. and ID is unique/auto_increment:
SELECT MAX(ID) FROM tablename
Solution 3:[3]
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:[4]
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:[5]
SELECT MAX(id) FROM TABLENAME
This identifies the largest id and returns the value
Solution 6:[6]
SELECT MAX(ID) FROM tablename LIMIT 1
Use this query to find the highest ID in the MySQL table.
Solution 7:[7]
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:[8]
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:[9]
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:[10]
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: 152msSELECT 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.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow