October 10, 2005 at 1:21 pm
I am trying to construct a query that will return data for display in a hierarchical grid. The query will contain two selects, which returns data in a parent / child relationship, but the twist is I need the 'oldest child' (when it exists) in the first dataset and the parent and all 'younger siblings' in the second. This is audit type data so we want to show the latest record as the parent and all precursors as children.
Table schema is:
RowID,,...,DateUpdated, ParentID
For the parent row, the RowID and ParentID are identical. For all children, they will have a unique RowID.
Less than 5% of the records will have children and they could have any number of children. For those that have children, how do I construct the query to return only the latest DateUpdated child and for those without children, it returns the parent?
The second dataset would contain all of the records not in the first, so I would use the same type of query with a NOT IN () clause.
October 10, 2005 at 2:04 pm
-- SQL 1 Parent and oldest child
select Parent.*, OldestChild.*
from MyTable as Parent
join MyTable as OldestChild
on Parent.ParentId = OldestChild.ParentId
join (select ParentId
, MAX(DateUpdated) as DateUpdated
from MyTable
group by ParentId)
as Oldest
on OldestChild.ParentId = Oldest.ParentId
and OldestChild.DateUpdated= Oldest.DateUpdated
where Parent.RowId = Parent.ParentId
-- SQL 2 children except oldest
select Child.*
from MyTable as Child
join (select ParentId
, MAX(DateUpdated) as DateUpdated
from MyTable
group by ParentId)
as Oldest
on Child.ParentId = Oldest.ParentId
and Child.DateUpdated Oldest.DateUpdated
SQL = Scarcely Qualifies as a Language
October 10, 2005 at 2:51 pm
Thank you for the quick reply - that seems to work - with one caveat, the Parent record needs to have DateChanged set (currently it was NULL).
I can modify the data to set DateChanged for the parent if need be - still trying to tweak the query to accomodate the NULL, but not sure that is doable.
Thanks again-
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply