152

How would I do something like this?

SQL SELECT row FROM table WHERE id=max(id)
gunr2171
  • 10,315
  • 25
  • 52
  • 75
Victor Kmita
  • 1,775
  • 2
  • 11
  • 8
  • Dup of [mysql: select max(score) doesn't return the relevant row data. how to solve the isssue?](http://stackoverflow.com/questions/2081211/mysql-select-maxscore-doesnt-return-the-relevant-row-data-how-to-solve-the-i), [Control which row is returned by a group by](http://stackoverflow.com/questions/537223/mysql-control-which-row-is-returned-by-a-group-by) – outis Oct 14 '11 at 20:40

6 Answers6

244

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
unutbu
  • 711,858
  • 148
  • 1,594
  • 1,547
  • 7
    @AlirezaSoori: Despite the name, `id` is just a column in a table. There is no guarantee that the values in the `id` column have to be unique. – unutbu Jun 22 '13 at 11:26
  • 1
    @unutbu Assuming that `id` is not a primary or unique key :) Given the name, there's a reasonable chance that it is. It's also worth noting that depending on the DBMS you're using, the approach with the subselect may be much less efficient. – Michael Mior Feb 07 '14 at 16:25
  • 3
    @MichaelMior: `id` could be a foreign key, in which case it may not be unique. I did some benchmarking using `set profiling = 1; ...; show profiles` and it appears our solutions have the same performance using MySQL. For my own knowledge, do you know what DBMS has poorer performance for subselects? – unutbu Feb 07 '14 at 20:08
  • 1
    It could be a foreign key, but as I said, I'm just guessing based on the name that it isn't. MySQL is historically known to have bad performance with subselects. That has vastly improved in newer versions though, so depends what version you're using. However, rethinking it, this particular query may be OK. Although running a query a couple times with profiling doesn't necessarily say much about relative performance. – Michael Mior Feb 08 '14 at 23:38
153

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.

Roland Ruul
  • 1,142
  • 8
  • 15
Michael Mior
  • 26,133
  • 8
  • 80
  • 110
  • 1
    To specifically do what the OP is asking, I'd do this. But the other answers do provide a better education on SQL structure :) – MatBailie Sep 30 '11 at 01:05
  • @Dems How so? No explanations are given on any other answer? I of course am guilty of that as well :( – Michael Mior Sep 30 '11 at 04:52
  • Just that other questions correct the syntax without refactoring the logic. So, the OP learns how to state that specific sql correctly. – MatBailie Sep 30 '11 at 07:26
  • Fair point :) Although other answers are arguably still correcting the logic. – Michael Mior Sep 30 '11 at 11:47
  • What about performance? I got here with this kind of query already working for me, but I was wondering if that's the right way. Isn't ORDER BY an O(n * log n) operation? – dhill May 04 '15 at 12:58
  • @dhill You're assuming that the data needs to be sorted. If you assume `id` is the primary key, this is not the case and this is likely an O(1) operation. – Michael Mior May 04 '15 at 21:46
28
SELECT * 
FROM table 
WHERE id = (SELECT MAX(id) FROM TABLE)
shA.t
  • 15,232
  • 5
  • 47
  • 95
Russell Shingleton
  • 3,096
  • 1
  • 20
  • 29
  • @shA.t `SELECT entry FROM table WHERE id = MAX(id)` wouldn't work?! – oldboy Mar 21 '18 at 17:46
  • @shA.t Also, what I'm trying to do something like the following: `SELECT entry_time FROM users_unverified WHERE num_id = (SELECT MAX(num_id) FROM users_unverified WHERE account_email = :account_email)` whereby i just need the `entry_time` of the most recent entry in the database. Is that statement sufficient or should it be: `SELECT entry_time FROM users_unverified WHERE num_id = (SELECT MAX(num_id) FROM users_unverified) AND account_email = :account_email` – oldboy Mar 21 '18 at 17:54
  • There is no trusted meaning for most recent entry in a query result, you need to have a field for insertion time and so on. BTW, please ask your question separately, I hope you will get more attentions -HTH ;). – shA.t Mar 26 '18 at 06:24
17

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);
yizzlez
  • 8,449
  • 4
  • 27
  • 43
CakeLikeBoss
  • 232
  • 2
  • 2
  • 18
    `ORDER BY` will not do a full scan if you assume that `id` is the primary key of the table. (And if it isn't, it's rather poorly named.) If it's not, how do you expect `MAX(id)` to work without a full table scan? If there's no index, every value must still be checked to find the maximum. – Michael Mior May 04 '15 at 21:47
  • @CakeLikeBoss well I actually tried "order by " query and your "SELECT * FROM table WHERE id = (SELECT MAX(id) FROM table);" query over a table of 114 rows while this query took exactly 0.0004 sec every time while the second query took from 0.0007 to 0.0010 secs I repeated this several times – prabhjot Jun 05 '17 at 10:27
2

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.

sumit kumar
  • 130
  • 1
  • 11
0

Try with this 

 SELECT top 1  id, Col2,  row_number() over (order by id desc)  FROM Table
Wella
  • 1,165
  • 2
  • 14
  • 24