Hello,

I would need to do a query which I think is VERY TRICKY and yet possible to do using a self join.



TABLE :

ID Condition ConditionString Parent
1 Policy Policy1 0
2 Time 2005-02-02 1
3 Rule Rule1 1
4 Rule Rule2 1
5 Policy Policy1 0
6 Time 2005-01-02 5
7 Rule Rule1 5
8 Rule Rule2 5
9 Policy Policy2 0
10 Time 2005-02-02 9
11 Rule Rule1 9
12 Rule Rule2 9
13 Policy Policy1 0
14 Time 2005-02-02 13
15 Rule Rule1 13



The need is as follows:

I would need to find those RECENT parent IDs (1 and 13) which had
Main Condition as "Policy1" and sub condition has "Rule1".


In the above case, I have 4 groups of records.
1 with Policy1 and rules 1 & 2 entered on 2005-02-02

13 with Policy1 and rule 1 entered on 2005-02-02

5 doesn't fit in as it was entered on an earlier date.

Likewise, 9 doesn't fit in as it has a different policy though the
same rule.

Is this possible in a single query? How do I do it?

Thanks in advance
Cel

You should be able to do this, but the more important question is why have you structured your database like this??? As soon as you noticed that you were storing dates/times in a string type column, alarms should have been going off. If you don't restructure your tables, then your query is going to be overly complex and inefficient. But if changing it is not a possibility, check out the date and time functions to cast your date strings and solve your ordering problem.