Re: [PHP] [php] most recent row from table
On Tue, Jun 02, 2009 at 12:33:24PM -0400, Andrew Ballard wrote:
> On Tue, Jun 2, 2009 at 12:17 PM, Paul M Foster
> 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
Nothing, if your fields happen to work out so that you can do it that
way. The key in your case is using the DESC keyword, which puts the
newest date at the top/front of the array.
Paul
--
Paul M. Foster
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] [php] most recent row from table
On Tue, Jun 2, 2009 at 12:17 PM, Paul M Foster 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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] [php] most recent row from table
On Tue, Jun 2, 2009 at 12:19 PM, Paul M Foster wrote:
> On Tue, Jun 02, 2009 at 11:58:07AM -0400, Bastien Koert wrote:
>
>> On Tue, Jun 2, 2009 at 11:49 AM, 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
>> > --
>> > www.bemycandy.com
>> >
>>
>>
>> select top 1 from table where id = xx order by date_field desc
>
> What database(s) support a "TOP" clause in SELECT statements?
>
> Paul
>
> --
> Paul M. Foster
Microsoft SQL Server. It's not as flexible as MySQL's LIMIT statement, though.
Andrew
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] [php] most recent row from table
On Tue, Jun 02, 2009 at 11:58:07AM -0400, Bastien Koert wrote:
> On Tue, Jun 2, 2009 at 11:49 AM, 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
> > --
> > www.bemycandy.com
> >
>
>
> select top 1 from table where id = xx order by date_field desc
What database(s) support a "TOP" clause in SELECT statements?
Paul
--
Paul M. Foster
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] [php] most recent row from table
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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] [php] most recent row from table
On Tue, Jun 2, 2009 at 11:49 AM, 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
> --
> www.bemycandy.com
>
select top 1 from table where id = xx order by date_field desc
--
Bastien
Cat, the other other white meat
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
