Selecting Most Recent Item From Database

[adam_shep]

I would like to use a SELECT statement that would get the most recently added item to a MySQL database. How would I do this? What type of MySQL column in my table would I use to track what has been most recently added?

Thanks for the help,

Adam

[Vinay]

keep an ID .. which is a unique auto_increasing PRIMARY column

then you can do

SELECT * FROM table ORDER BY DESC ID

that will arrange ID into desc order, where the highest value (the newest entry) will be on top ...

------------------
SitePoint Moderator
myONLYhost.com

[adam_shep]

But I don't want to select all the entries, only the most recent.

Is there no way to just select one entry.

SELECT something FROM mytable WHERE myfield equals something?

Thanks

[a00]

Like this ?
select * from table ORDER BY timestamp LIMIT 1;

[Kevin Yank]

a00 has got the right idea.

First of all, Vinay's suggestion will not work, since more recent versions of MySQL will fill in "holes" in the auto-increment order. For example, if you delete entry 25 of 30, then the next inserted entry will be given ID 25 to fill in the hole that was left by the deleted item. In this case, entry 30 would be incorrectly identified as the most recent entry.

a00's suggestion to use a TIMESTAMP column is closer to what you need. In MySQL, a column of type TIMESTAMP will be assigned the current date and time (in the format "YYYYMMDDHHMMSS") both when the entry is inserted and whenever one of the other columns is updated, as long as you don't specify a value for the TIMESTAMP column yourself.

You can use such a column to select the most recently inserted or updated entries using the following SELECT query:

SELECT * FROM tblName ORDER BY timeStampColName DESC LIMIT 1

You can change the limit if, for example, you want the five most recently inserted or updated entries.

Unfortunately, this is no good either because you asked for the most recently added item, and the timestamp column method will also see updated entries as "new".

What I'd suggest is to use a column of type DATE and assign it a value of CURDATE() when you insert a new item. Then you can use the above query (using your date column in place of the timestamp column) to pick the most recently added entry or entries.


------------------
-Kevin Yank.
http://www.SitePoint.com/
Helping Small Business Grow Online!