MySQL GROUP BY ignoring ORDER BY

Hi,

I am having two tables (table1, the primary table and table2 which holds
data referring to table1) which I am trying to combine with a LEFT JOIN.

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=table1.id
GROUP BY table1.id
ORDER BY table2.time DESC

The problem now is that it seems using a GROUP BY ignores the indicated
ORDER BY statement as I am not getting the last entry's date.

Does anybody have an idea on how to solve this?

Thanks,
Alexander

Re: MySQL GROUP BY ignoring ORDER BY

Alexander wrote:
> Hi,
>
> I am having two tables (table1, the primary table and table2 which
> holds data referring to table1) which I am trying to combine with a
> LEFT JOIN.
> 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=table1.id
> GROUP BY table1.id
> ORDER BY table2.time DESC
>
> The problem now is that it seems using a GROUP BY ignores the
> indicated ORDER BY statement as I am not getting the last entry's
> date.
> Does anybody have an idea on how to solve this?
>
> Thanks,
> Alexander

What you have said doesn't make sense:

| 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".
Did you miss some words out? Please rephrase this as it currently does not
make sense??

| The problem now is that it seems using a GROUP BY ignores the indicated
| ORDER BY statement as I am not getting the last entry's date.
How would and ORDER BY prevent you getting a date? All the order by would do
is set the order in which the results are presented, it would not affect
which results you would see.

Re: MySQL GROUP BY ignoring ORDER BY

On Sat, 14 Jul 2007 15:08:10 +0200, Alexander =

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