Subversion Repositories DevTools

/* Find release name */
SELECT DISTINCT 
           MIN(qry.DISPLAY_ORDER) OVER (PARTITION BY qry.RTAG_NAME) AS DISPLAY_MIN,
           qry.RTAG_ID,
           qry.RTAG_NAME,
       qry.proj_name,
       qry.proj_id,
       qry.official
  FROM (
  
                /* Exact match */
                SELECT 1 AS DISPLAY_ORDER,
                           rt.RTAG_ID,
                           rt.RTAG_NAME,
               p.proj_name,
               p.proj_id,
               rt.official
                  FROM RELEASE_TAGS rt, PROJECTS p
                 WHERE UPPER(rt.RTAG_NAME) = UPPER(:KEYWORD)
         AND   p.proj_id = rt.proj_id
                 
                UNION
                  
                /* Starts with */  
                SELECT 2 AS DISPLAY_ORDER,
                           rt.RTAG_ID,
                           rt.RTAG_NAME,
               p.proj_name,
               p.proj_id,
               rt.official
                  FROM RELEASE_TAGS rt, PROJECTS p
                 WHERE UPPER(rt.RTAG_NAME) LIKE UPPER(:KEYWORD) ||'%'
         AND   p.proj_id = rt.proj_id
                 
                UNION
                  
                /* Ends with */  
                SELECT 3 AS DISPLAY_ORDER,
                           rt.RTAG_ID,
                           rt.RTAG_NAME,
               p.proj_name,
               p.proj_id,
               rt.official
                  FROM RELEASE_TAGS rt, PROJECTS p
                 WHERE UPPER(rt.RTAG_NAME) LIKE '%'|| UPPER(:KEYWORD) 
         AND   p.proj_id = rt.proj_id

                UNION
                  
                /* Middle of word */  
                SELECT 4 AS DISPLAY_ORDER,
                           rt.RTAG_ID,
                           rt.RTAG_NAME,
               p.proj_name,
               p.proj_id,
               rt.official
                  FROM RELEASE_TAGS rt, PROJECTS p
                 WHERE UPPER(rt.RTAG_NAME) LIKE '%'|| UPPER(:KEYWORD) ||'%'              
         AND   p.proj_id = rt.proj_id
                 
       ) qry
ORDER BY DISPLAY_MIN, UPPER(qry.RTAG_NAME)