Query "hightest value" contents of each "folder"?

[Robin Tucker]

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, 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

[David Portas]

Another way:

SELECT T1.id_parent, MAX(T1.id) AS id, T1.date
FROM TestQuery AS T1
WHERE T1.date =
(SELECT MAX(date)
FROM TestQuery AS T2
WHERE T1.id_parent = T2.id_parent)
GROUP BY T1.id_parent, T1.date

--
David Portas
SQL Server MVP
--

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[David Portas]

Try this:

SELECT T1.id_parent, MAX(T1.id) AS id, T1.date
FROM TestQuery AS T1
LEFT JOIN TestQuery AS T2
ON T1.id_parent = T2.id_parent
AND T1.date < T2.date
WHERE T2.date IS NULL
GROUP BY T1.id_parent, T1.date

--
David Portas
SQL Server MVP
--

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Toucan]

here is a related article:
subject: select first record from a group ordered by 3 columns
date: Nov 27 2002, 5:01 am

http://groups-beta.google.com/group/...3b16be2b945c0f

David Portas wrote:

Another way:

SELECT T1.id_parent, MAX(T1.id) AS id, T1.date
FROM TestQuery AS T1
WHERE T1.date =
(SELECT MAX(date)
FROM TestQuery AS T2
WHERE T1.id_parent = T2.id_parent)
GROUP BY T1.id_parent, T1.date

--
David Portas
SQL Server MVP
--

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!