Posted by Rik on 07/16/07 18:46

On Sat, 14 Jul 2007 15:08:10 +0200, Alexander <postmaster@example.com> =

wrote:

>
> Hi,
>
> I am having two tables (table1, the primary table and table2 which hol=
ds =

> data referring to table1) which I am trying to combine with a LEFT JOI=
N.
>
> Now I'd like to retrieve all values from table1 along with the number =
of =

> their entries in table2, sorted descending by the table2's timestamp =

> however to get the "last".
>
> SELECT table1.*, table2.time, COUNT(table2.t1ref)
> FROM table1
> LEFT JOIN table2 ON table2.t1ref=3Dtable1.id
> GROUP BY table1.id
> ORDER BY table2.time DESC
>
> The problem now is that it seems using a GROUP BY ignores the indicate=
d =

> ORDER BY statement as I am not getting the last entry's date.
>
> Does anybody have an idea on how to solve this?

SELECT table1.*, MAX(table2.time) as 'time',COUNT(table2.t1ref)
FROM table1
LEFT JOIN table 2 ON table2.t1ref =3D table1.id
GROUP BY table1.id


-- =

Rik Wasmus

• Next in forum: Re: rename all tables in database newbie question
• Prev in forum: Re: Race condition when inserting data / MySQL 4.0+, MyISAM tables
• Thread view: Re: MySQL GROUP BY ignoring ORDER BY

[Reply to this message]