CODEWITHSUNDEEP
mysqlsql
Victor Kmita
Victor Kmita

Reputation: 1945

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

How would I do something like this?

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

Upvotes: 168

Views: 598668

Answers (7)

bArraxas
bArraxas

Reputation: 664

For more complex request, noone solution works.

Here a more complex query :

SELECT id, affaire_id, planned_at, description, updated_at, GROUP_CONCAT(CONCAT('"', DATE_FORMAT(planned_at, '%Y-%m-%d'), '"') ORDER BY planned_at SEPARATOR ', ') dates
FROM `affaires_actes`
GROUP BY affaire_id
HAVING id = MAX(id)

The solution is move all group functions into a subquery like that :

SELECT id, a.affaire_id, planned_at, description, updated_at, dates
FROM `affaires_actes` a
LEFT JOIN (
  SELECT affaire_id, GROUP_CONCAT(CONCAT('"', DATE_FORMAT(planned_at, '%Y-%m-%d'), '"') ORDER BY planned_at SEPARATOR ', ') dates
  FROM affaires_actes a1
  GROUP BY affaire_id
) aa ON aa.affaire_id = a.affaire_id
WHERE a.id = aa.maxId

Upvotes: 0

sumit kumar
sumit kumar

Reputation: 150

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.

Upvotes: 2

Michael Mior
Michael Mior

Reputation: 28752

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.

Upvotes: 161

Russell Shingleton
Russell Shingleton

Reputation: 3196

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

Upvotes: 29

Wella
Wella

Reputation: 1476

Try with this 

 SELECT top 1  id, Col2,  row_number() over (order by id desc)  FROM Table

Upvotes: 0

CakeLikeBoss
CakeLikeBoss

Reputation: 232

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);

Upvotes: 17

unutbu
unutbu

Reputation: 880577

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

Upvotes: 266

Related Questions