SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    May 2000
    Location
    Eugene, OR
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  2. #2
    SitePoint Evangelist
    Join Date
    May 2000
    Location
    Canada
    Posts
    533
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  3. #3
    SitePoint Zealot
    Join Date
    May 2000
    Location
    Eugene, OR
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SitePoint Member
    Join Date
    Jul 2000
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Like this ?
    select * from table ORDER BY timestamp LIMIT 1;

  5. #5
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •