[svn.haxx.se] · SVN Dev · SVN Users · SVN Org · TSVN Dev · TSVN Users · Subclipse Dev · Subclipse Users · this month's index

SQL to get WORKING from NODES

From: Philip Martin <philip.martin_at_wandisco.com>
Date: Fri, 24 Sep 2010 11:37:28 +0100

These two queries don't work:

 -- STMT_SELECT_WORKING_NODE_CHILDREN_1
 SELECT local_relpath FROM nodes
 WHERE wc_id = ?1 AND parent_relpath = ?2
   AND op_depth = (SELECT MAX(op_depth) FROM nodes
                   WHERE wc_id = ?1 AND parent_relpath = ?2 AND op_depth > 0);

 -- STMT_COUNT_WORKING_NODE_CHILDREN_1
 SELECT COUNT(*) FROM nodes
 WHERE wc_id = ?1 AND parent_relpath = ?2
   AND op_depth = (SELECT MAX(op_depth) FROM nodes
                   WHERE wc_id = ?1 AND parent_relpath = ?2 AND op_depth > 0);

The problem is that they determine the higest op_depth for the
children and return only the children with that op_depth. This
happens to work if all the children have the same highest op_depth but
that's just an accident.

I can fix the first one as follows:

 -- STMT_SELECT_WORKING_NODE_CHILDREN_1
 SELECT local_relpath FROM nodes
 WHERE wc_id = ?1 AND parent_relpath = ?2 AND op_depth > 0
 GROUP BY local_relpath;

but I don't know how to fix the second one. How do I count the number
of rows returned by that GROUP BY query?

This leads on to the problem of selecting just the highest op_depth
for each child. Is it possible to get one query to return just the
highest op_depth for each child?

-- 
Philip
Received on 2010-09-24 12:38:13 CEST

This is an archived mail posted to the Subversion Dev mailing list.