Posted by Paul Lautman on 07/14/07 22:57

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.

[Back to original message]