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

>> Can anyone tell me how to select the "most recent" date values from
a grouped query? Consider the following: .. <<

Those names were so horrible, even for a Newsgrup posting I had to change them. You also seem to believe that T-SQL and SQL are the same thing -- square brackets, proprietary syntax, reserved words as column names, no constraints, etc. This is the Theory group, so quit talking in a dialect. And you got the DDL wrong.

CREATE TABLE Tree
(node INTEGER NOT NULL,
 parent INTEGER, -- root is null!
 tree_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,  CHECK (<<no cycles>>), -- T-SQL needs a trigger here  CHECK (<<one root>>) -- T-SQL needs a trigger here );

You kept using non-relational terms in your specs and never gave any sample data; is this what you meant?

>> What I want to do is find the highest tree_date for each sub-tree
and the root of the subtree. <<

Assuming that I guessed right, your approach is wrong.

>> I think I first need to group by parent, sort by tree_date
(descending), then select the TOP 1 from this query, but how to aggregate them so I get the TOP 1 for each parent? <<

Do you really hate relational databases so much that you would revert to (ugggh!) thinly disguised procedural programming?

1. Did you want the oldest IMMEDIATE child? That is easy:

   SELECT parent, MAX(tree_date)
     FROM Tree
    GROUP BY parent;

2) Did you want the oldest child in the Entire subtree roots at each parent? That requires a cursor to do a procedural tree traversal. This is the nature of the Adjacency list model. But if you had a nested sets model instead, you would write:

1. SELECT R.node AS root, MAX(C.tree_date) AS child_date FROM Tree AS C LEFT OUTER JOIN Tree AS R ON R.lft
   • (SELECT MAX(lft) FROM Tree AS S WHERE C.lft > S.lft AND C.lft < S.rgt);
2. SELECT R.node AS root, MAX(T1.tree_date) AS subordinate_date FROM Tree AS T1, Tree AS R WHERE T1.lft BETWEEN R.lft AND R.rgt GROUP BY R.node;

The root node is part of the subtree in (2), but not (1). You did not give specs, so I had to guess and I did it both ways. Modifications are easy.

I have a whole book on TREES & HIERARCHIES IN SQL you might want to look at. Received on Tue Nov 23 2004 - 05:07:56 CET