Hmmm...I'm not sure this has any advantages over my single joined query. I guess your suggestion is to get rid of the Paths table, but this seems to make the ancestors and descendents queries less efficient. The only real problem I see with my approach is when you have a composite key in the Groupable table.

R.

-----Original Message-----
From: Svein Erling Tysvær [mailto:svein.erling.tysvaer@...]
Sent: Saturday, May 03, 2003 9:50 AM
To: ib-support@yahoogroups.com
Subject: RE: [ib-support] COUNT DISTINCT on a composite key

>{for me} a fascinating puzzle which I don't have time to solve. I started
>to think about it (actually trying to work out what you needed the count
>for) and, sorry, it's one I can't spare that much time for.

An interesting problem that Helen ain't got time to solve! Well, I just got a pretty simple idea, but it requires a bit more than just a query:

Create a recursive stored procedure along the lines of

CREATE PROCEDURE AllNodes(ANode: Integer)
RETURNS ID INTEGER
AS
DECLARE VARIABLE CurrentNode INTEGER
BEGIN
FOR SELECT ParentID FROM Links WHERE ChildID = :ANode INTO :CurrentNode DO
BEGIN
AllNodes(:CurrentNode);
END
ID = :ANode;
SUSPEND;
END;

Note that I never write stored procedures and haven't even got Firebird on this machine, so the syntax should contain some errors. My thought is that if you call AllNodes(3), that would call AllNodes(2) and AllNodes(1), which in turn would call AllNodes(0) and you would get the following output (well, I haven't thought too much about in which order they would appear):

0
1
0
2
3

Calling SELECT COUNT(DISTINCT ID) FROM AllNodes(3) should then return 4. Since your only interested in the number of parents, you would most likely want to call SELECT COUNT(DISTINCT ID) - 1 FROM AllNodes(3) which should give you 3.

If you get problems with the SP syntax, then I'm sure someone else on this list can help you out.

HTH,
Set

-Fulda is only two weeks away.



Yahoo! Groups Sponsor
ADVERTISEMENT

<http://rd.yahoo.com/M=231971.3069703.4522067.1261774/D=egroupweb/S=1705115386:HM/A=1554463/R=4/id=noscript/*http://shop.store.yahoo.com/cgi-bin/clink?proflowers2+shopping:dmad/M=231971.3069703.4522067.1261774/D=egroupweb/S=1705115386:HM/A=1554463/R=5/1051979804+http://us.rmi.yahoo.com/rmi/http://www.proflowers.com/rmi-unframed-url/http://www.proflowers.com/freevase/index.cfm%3FREF=FGVYahooEgroupsLRECgif>
<http://us.adserver.yahoo.com/l?M=231971.3069703.4522067.1261774/D=egroupmail/S=:HM/A=1554463/rand=593767023>

To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service <http://docs.yahoo.com/info/terms/> .




[Non-text portions of this message have been removed]