By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,827 Members | 2,195 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,827 IT Pros & Developers. It's quick & easy.

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

P: n/a

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


Jul 20 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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!
Jul 20 '05 #2

P: n/a
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!
Jul 20 '05 #3

P: n/a
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!


Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.