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

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

However, when I write the following (because I am most interested in the unique ID of each record, not the date), I have all records returned, which is wrong:

SELECT TestQuery.ID, MAX (TestQuery.Date) as "Most Recent" FROM
TestQuery
GROUP BY
TestQuery.ID_Parent,
TestQuery.ID

Note I have to put TestQuery.ID in the group by section because I have it in the SELECT.

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 :)

This is what I can't seem to do!

"Alan" <alan_at_erols.com> wrote in message news:30eforF2uf0nuU1_at_uni-berlin.de...
>
> "Robin Tucker" <idontwanttobespammedanymore_at_reallyidont.com> wrote in
> message news:cnsvgc$3gr$1$8300dec7_at_news.demon.co.uk...
>> Hi,
>>
>>
>> Can anyone tell me how to select the "most recent" date values from a
>> grouped query? Consider the following:
>>
>> CREATE TABLE [dbo].[TestQuery] ( [ID] [int] NOT NULL , [ID_Parent] [int]
> NOT
>> NULL , [Date] [datetime] NOT NULL ) ON [PRIMARY]
>>
>> This is a simplified adjacency list. What I want to do is find the
> highest
>> valued item by date for each sub-tree. In other words, the single
>> highest
>> date item when This.ID_Parent = That.ID_Parent. I think I first need to
>> group by ID_Parent, sort by Date (descending), then select the TOP 1 from
>> this query,
>> but how to aggregate them so I get the TOP 1 for each ID_Parent?
>>
>>
>> Thanks for any help you can give me,
>>
>>
>>
>>
>>
>> Robin
>>
>>
>
> Look up MAX() and GROUP BY
>
>
Received on Mon Nov 22 2004 - 17:35:14 CET