How to write oracle sql query for selecting single record which is having highest status-CodePudding

Assume that have a scenario like in request table with same request Id I may have multiple records with different statuses status like Draft, InProgress, Approved, Completed . we need to fetch single highest status record. Here preferred order is Completed -> Approved -> InProgress -> Draft.

if have three records like one is with InProgress, one with Approved and another one is with Completed, then among these three in need fetch only one record which have highest status Completed.

if have two records like one is with InProgress and another one is with Draft, then among these two in need fetch only one record which have highest status InProgress.

Could any one please suggest me on this ?

CodePudding user response：

Use the ROW_NUMBER analytic function to order the rows based on a CASE expression that converts your string values to priorities:

SELECT *
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (
           PARTITION BY request_id
           ORDER BY CASE status
                    WHEN 'Completed'  THEN 1
                    WHEN 'Approved'   THEN 2
                    WHEN 'InProgress' THEN 3
                    WHEN 'Draft'      THEN 4
                    ELSE 5
                    END
         ) as rn
  FROM   table_name t
)
WHERE  rn = 1;

CodePudding user response：

Its a bit of an heinous solution (tested on postgresql) - but you can convert your textual status into a number with a CASE statement and then use that plus a subquery to get the highest status:

SELECT rt.* 
FROM
    (SELECT 
         id, 
         MAX(CASE 
                 WHEN status = 'Draft' THEN 0 
                 WHEN status = 'InProgress' THEN 10 
                 WHEN status = 'Approved' THEN 20 
                 WHEN status = 'Completed' THEN 30 
             END) AS msid 
     FROM 
         request_table 
     GROUP BY 
         id) max_per_id 
INNER JOIN 
    request_table rt ON max_per_id.id = rt.id 
                     AND max_per_id.msid = CASE WHEN rt.status='Draft' THEN 0 WHEN rt.status='InProgress' then 10 WHEN rt.status='Approved' THEN 20 WHEN rt.status='Completed' then 30 END

The subquery

SELECT 
    id, 
    MAX(CASE 
            WHEN status = 'Draft' THEN 0 
            WHEN status = 'InProgress' THEN 10 
            WHEN status = 'Approved' THEN 20 
            WHEN status = 'Completed' THEN 30 
        END) AS msid 
FROM 
    request_table 
GROUP BY 
    id) max_per_id

provides the highest numeric status for each id. That then gets joined on the id and the numeric version of the status with the original table