--- a/flys-artifacts/doc/conf/datacage.sql	Thu Aug 18 14:13:35 2011 +0000
+++ b/flys-artifacts/doc/conf/datacage.sql	Thu Aug 18 14:19:30 2011 +0000
@@ -67,6 +67,25 @@
     UNIQUE (out_id, num, name)
 );
 
+CREATE VIEW master_artifacts AS
+    SELECT a2.id             AS id,
+           a2.gid            AS gid,
+           a2.state          AS state,
+           a2.creation       AS creation,
+           ci2.collection_id AS collection_id
+    FROM   collection_items ci2 
+           JOIN artifacts a2 
+             ON ci2.artifact_id = a2.id 
+           JOIN (SELECT ci.collection_id AS c_id, 
+                        MIN(a.creation)  AS oldest_a 
+                 FROM   collection_items ci 
+                        JOIN artifacts a 
+                          ON ci.artifact_id = a.id 
+                 GROUP  BY ci.collection_id) o 
+             ON o.c_id = ci2.collection_id 
+    WHERE  a2.creation = o.oldest_a;
+
+-- DROP VIEW master_artifacts;
 -- DROP SEQUENCE USERS_ID_SEQ;
 -- DROP SEQUENCE COLLECTIONS_ID_SEQ;
 -- DROP SEQUENCE ARTIFACTS_ID_SEQ;