Re: Simpleish query I can't get my head around :)

Super. Works fine. Thanks.

"Dieter Nöth" <dnoeth_at_gmx.de> wrote in message news:30eji3F2vesraU1_at_uni-berlin.de...
> Robin Tucker schrieb:
>
>> Ok, using MAX and GROUP BY, this query gives me the correct results (91
>> records returned which is correct in my example) - ie. it returns 91
>> "dates" in the "Most Recent" column.
>>
>> SELECT MAX (TestQuery.Date) as "Most Recent"
>> FROM
>> TestQuery
>> GROUP BY
>> TestQuery.ID_Parent
> ...
>> I don't want to know the ID_Parent and DATE, I would like to know the ID
>> of the ITEM that has the MAX date for each ID_Parent :)
>
> You need a correlated subquery:
> select *
> from testquery t1
> where date =
> (
> SELECT
> MAX (TestQuery.Date) as "Most Recent"
> FROM
> TestQuery t2
> where t1.ID_Parent = t2.ID_Parent
> )
>
> or a join:
> select t1.*
> from testquery t1 join
> (
> SELECT
> ID_Parent,
> MAX (TestQuery.Date) as "Most Recent"
> FROM
> TestQuery
> ) t2
> on t1.ID_Parent = t2.ID_Parent
> and t1.Date = t2.date
>
> Dieter
Received on Mon Nov 22 2004 - 18:02:06 CET