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

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 - 17:47:57 CET