How do I select an entire row which has the largest ID in the table?

MysqlSql

Mysql Problem Overview

How would I do something like this?

SQL SELECT row FROM table WHERE id=max(id)

Mysql Solutions

Solution 1 - Mysql

You could use a subselect:

SELECT row 
FROM table 
WHERE id=(
    SELECT max(id) FROM table
    )

Note that if the value of max(id) is not unique, multiple rows are returned.

If you only want one such row, use @MichaelMior's answer,

SELECT row from table ORDER BY id DESC LIMIT 1

Solution 2 - Mysql

You could also do

SELECT row FROM table ORDER BY id DESC LIMIT 1;

This will sort rows by their ID in descending order and return the first row. This is the same as returning the row with the maximum ID. This of course assumes that id is unique among all rows. Otherwise there could be multiple rows with the maximum value for id and you'll only get one.

Solution 3 - Mysql

SELECT * 
FROM table 
WHERE id = (SELECT MAX(id) FROM TABLE)

Solution 4 - Mysql

You can not give order by because order by does a "full scan" on a table.

The following query is better:

SELECT * FROM table WHERE id = (SELECT MAX(id) FROM table);

Solution 5 - Mysql

One can always go for analytical functions as well which will give you more control

select tmp.row from ( select row, rank() over(partition by id order by id desc ) as rnk from table) tmp where tmp.rnk=1

If you face issue with rank() function depending on the type of data then one can choose from row_number() or dense_rank() too.

Solution 6 - Mysql

Try with this

 SELECT top 1  id, Col2,  row_number() over (order by id desc)  FROM Table
Categories
Recommended Mysql Solutions
Recommended Sql Solutions

Previous Solution:

grep using a character vector with multiple patterns

RRegex

Next Solution:

How to redirect Valgrind's output to a file?

RedirectValgrind

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionVictor KmitaView Question on Stackoverflow
Solution 1 - MysqlunutbuView Answer on Stackoverflow
Solution 2 - MysqlMichael MiorView Answer on Stackoverflow
Solution 3 - MysqlRussell ShingletonView Answer on Stackoverflow
Solution 4 - MysqlCakeLikeBossView Answer on Stackoverflow
Solution 5 - Mysqlsumit kumarView Answer on Stackoverflow
Solution 6 - MysqlWellaView Answer on Stackoverflow