Is there a less hacky way to do this in MySQL?

Question

I need to get the last inserted product by a user. This is what I've came up with

   $query = 'SELECT id
                 FROM products
                 WHERE user_id = ?
                ORDER BY id DESC
                       LIMIT 1';

It should work because the id is auto increment. To me though, it feels hacky. Is there a better way to do this?

I can't use mysql_last_insert_id() or anything like that because I need the last product by a specific user.

Answer 1

you could use

SELECT MAX(id) FROM ...

Not sure how much better that would be, tho :)

Answer 2

Use:

  SELECT MAX(t.id) AS id
    FROM PRODUCTS t
   WHERE t.user_id = ?

MAX is an aggregate function, returning the maximum value of the column specified. Because the query is only returning one column - the one the aggregate function is being used on - the GROUP BY clause doesn't need to be specified. But there's no harm in using:

  SELECT MAX(t.id) AS id
    FROM PRODUCTS t
   WHERE t.user_id = ?
GROUP BY t.user_id

Answer 3

I would add a DATETIME column to your table for this purpose. I personally don't feel comfortable relying on the behavior of AUTO_INCREMENT from the standpoint of ordering. Besides, its purpose is to "generate a unique identity for new rows." Aside from that, you shouldn't really care about the value.

Answer 4

I Agree with Datetime Column option. for e.g. you have column named LastAddedDate Datetime then you can make query like

Select top(1) Products.Id from Products where User_Id=@UserId order by LastAddedDate,Id Desc

or else in case of id also you can write query

Select top(1) Products.Id from Products where User_Id=@UserId order by Id Desc