Subversion Repositories DevTools

-- Determine the projects that have release mertics
-- Select the most recently built release for each project
-- Used in the Release Statistics summary page
WITH ORDERED as (
SELECT p.proj_id,
       p.proj_name,
       rt.rtag_name,
       rm.rtag_id,
       rm.total_packages,
       rm.autobuilt,
       rm.lines_of_code,
       rm.unit_tested,
       rm.autotested,
       TO_CHAR( rm.last_build_time,'DD-Mon-YYYY HH24:MI:SS' ) AS last_build_time,
       ROW_NUMBER() OVER (PARTITION BY p.proj_id ORDER BY CASE
         WHEN rm.last_build_time IS NULL THEN 2 ELSE 1
       END,
       rm.last_build_time DESC
       ) AS rn
  FROM projects p,
       release_metrics rm,
       release_tags rt
 WHERE p.proj_id = rt.proj_id
   AND rm.rtag_id = rt.rtag_id
 ORDER BY
       Upper(p.PROJ_NAME),
       CASE
         WHEN rm.last_build_time IS NULL THEN 2 ELSE 1
       END,
       rm.last_build_time DESC,
       UPPER(rt.rtag_name),
       rm.rtag_id DESC
)
SELECT * from ORDERED where rn=1