31

I simply need to select the last entered row specified by condition, e.g:

SELECT ID from bugs WHERE user=Me

I need to return only the very last ID entered by user 'Me'. Is there a simple way to do this? Thank you.

user124384
  • 400
  • 1
  • 9
  • 22
Thomas
  • 2,575
  • 9
  • 31
  • 41
  • The best answer I found is the one by Pomyk [here](http://stackoverflow.com/questions/1388025/how-to-get-id-of-the-last-updated-row-in-mysql#answer-1751282) – aggregate1166877 Feb 15 '13 at 14:15
  • It appears that there is a [similar thread](https://stackoverflow.com/a/4073930/5810737) that as been answered. Could it be the solution you're looking for? – GlyphCat May 26 '19 at 12:03

9 Answers9

47

It would be best to have a TIMESTAMP column that defaults to CURRENT_TIMESTAMP .. it is the only true predictive behavior you can find here.

The second-best thing you can do is ORDER BY ID DESC LIMIT 1 and hope the newest ID is the largest value.

Matt
  • 43,482
  • 6
  • 101
  • 102
40

You can use ORDER BY ID DESC, but it's WAY faster if you go that way:

SELECT * FROM bugs WHERE ID = (SELECT MAX(ID) FROM bugs WHERE user = 'me')

In case that you have a huge table, it could make a significant difference.

EDIT

You can even set a variable in case you need it more than once (or if you think it is easier to read).

SELECT @bug_id := MAX(ID) FROM bugs WHERE user = 'me';
SELECT * FROM bugs WHERE ID = @bug_id;
pmrotule
  • 9,065
  • 4
  • 50
  • 58
  • I've actually timed it over few hundreds of queries. the `ORDER BY 'KEY' DESC LIMIT 1`, can be 200+ milliseconds (over 65%) faster then the `SELECT` of `SELECT`. then again, my DB is super small, the entire query does not takes more then 900 milliseconds, so I supposed on larger scale sorting might be slower. but then again, MYSQL implementations probably optimizing commonly used queries such as `ORDER BY 'KEY' DESC LIMIT 1`, so the end result will take much (much!) less then actually sorting then returning the last result. –  Jul 16 '14 at 20:00
  • 8
    Ok, so maybe it's faster with a small table. It's good to know! By cons, my solution is still a lot faster for a huge table. I tested it with a table with around 35,000 rows and it was 10 times faster then the solution with ORDER. – pmrotule Jul 16 '14 at 21:03
  • 1
    thats a great solution. Quick clarificaton: why can't we just do SELECT * FROM bugs WHERE ID = (SELECT MAX(ID) FROM bugs WHERE user = 'me') ...i.e. why do we need the initial user = 'me' ? – HopeKing May 11 '18 at 14:39
  • @HopeKing Good point. I wrote this answer a while ago so I'm not sure why I put `user = 'me'` twice. I tested it to be sure and edited my answer. Thanks for pointing it out. – pmrotule May 11 '18 at 15:01
31
SELECT MAX(ID) from bugs WHERE user=Me
SiLent SoNG
  • 4,270
  • 4
  • 27
  • 31
14

One way to accomplish that is to order you records and limit to 1. For example if you have the following table ('data'). 

    id | user | price
   -------------------
    1  |  me  | 40.23
    2  |  me  | 10.23

Try the following sql query

  select * from data where user='me' order by id desc limit 1
Hari Menon
  • 33,649
  • 14
  • 85
  • 108
9

In concurrency, the latest record may not be the record you just entered. It may better to get the latest record using the primary key.

If it is a auto increment field, use SELECT LAST_INSERT_ID(); to get the id you just created.

Darshana
  • 2,462
  • 6
  • 28
  • 54
Wayne
  • 681
  • 1
  • 6
  • 14
  • `LAST_INSERT_ID()` is global. It will not return a condition-based ID. – Matt May 05 '10 at 05:14
  • 7
    This **only** works if you do not perform inserting after the row you're trying to query. E.g., if someone registers (`insert into users ...`) then creates a post (`insert into posts ...`), `last_insert_id()` will refer to the last `posts.id`. At this point, there is no way to select the last user using `last_insert_id()`. – maček May 05 '10 at 05:17
6
SELECT * FROM `table_name` 
ORDER BY `table_name`.`column_name` DESC
LIMIT 1
roberto06
  • 3,844
  • 1
  • 18
  • 29
rommo roy
  • 71
  • 1
  • 4
0

Just after running mysql query from php

get it by 

$lastid=mysql_insert_id();

this give you the alst auto increment id value

pratik shourabh
  • 11
  • 1
  • no-one mentioned PHP. And even if they did, why would you propose to use the `mysql_` library which has been deprecated and unmaintained for such a long time, and finally removed in PHP 7? It's a security risk, and will not even work if you have PHP7 or above. No new code should be written using this obsolete library. If you are still using it, make a plan to migrate to mysqli or PDO, urgently. – ADyson Sep 04 '18 at 11:57
  • User Requested in a MYSQL not MYSQLI or PDO – pratik shourabh Sep 04 '18 at 12:51
  • Er, no. The question was about the SQL code itself. Look at all the other answers. Like I said, PHP was not even mentioned. The OP requested nothing in relation to what you've written. Using `mysql_` vs `mysqli_` or `PDO` doesn't change the underlying DBMS, it changes the PHP code library being used to connect to the database and execute queries. Each has different syntax and features, but they all execute SQL queries against a MySQL database server. From your comment it would appear you perhaps do not understand the distinction between the PHP library and the SQL language? – ADyson Sep 04 '18 at 12:57
0
MySqlCommand insert_meal = new MySqlCommand("INSERT INTO meals_category(Id, Name, price, added_by, added_date) VALUES ('GTX-00145', 'Lunch', '23.55', 'User:Username', '2020-10-26')", conn);
if (insert_meal .ExecuteNonQuery() == 1)
{
    long Last_inserted_id = insert_meal.LastInsertedId;
    MessageBox.Show(msg, "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else
            {
                MessageBox.Show("Failed to save meal");
            }
B.samu
  • 21
  • 4
-1

SELECT ID from bugs WHERE user=Me ORDER BY CREATED_STAMP DESC; BY CREATED_STAMP DESC fetches those data at index first which last created.

I hope it will resolve your problem

Kunwar Maurya
  • 76
  • 2