Re: [php] most recent row from table

On Tue, Jun 2, 2009 at 12:17 PM, Paul M Foster <paulf@quillandmouse.com> wrote: > On Tue, Jun 02, 2009 at 04:49:44PM +0100, HELP! wrote: > >> Hi, >> >> how do you select the most recent row from sql table if the date are stored >> this format  date('Y-m-d H:i:s')  e.g (2009-06-02 10:10:30) >> >> i have tried select top 1 from table where id = xx >> any solution > > This is the problem with databases. The two clauses for the SELECT > statement which assist here are LIMIT and OFFSET. But here's the > problem: You only want one record, which you can obtain using LIMIT = 1.. > But OFFSET only works if you know how many records are in the table, > which can change from second to second on a busy table. Moreover, there > isn't a specific command for obtaining the number of records in the > table. > > I'd suggest setting up some WHERE condition which limits the number of > records you obtain with the SELECT, ensure they are ordered with an > ORDER BY clause which puts the record you want at the bottom of the > array you get from SELECT. Count the records in the obtained SELECT > array, and take the last of these records. > > Paul > > -- > Paul M. Foster > What is wrong with this? (It's the MySQL equivalent of the query Bastien posted.) SELECT * FROM table ORDER BY date_field DESC LIMIT 1 Andrew