SQL Compare Rows With Duplicate IDs and Return One With Lowest Sequence Number

Question

Reaching out for help. I've seen plenty of answers on how to use DUPLICATE, but not quite how I need it. Let's say I have the result of query that looks like the following.

query result

  Incident_No   Open_Approval_Step      Approval_ID     
 ------------- -------------------- ------------------- 
            1                    3   Tech               
            1                    4   Cust_Serv          
            2                    1   Incident_Recorder  
            2                    2   Estimation         
            2                    3   Tech               
            3                    4   Cust_Serv          
            3                    5   Mgmt               
            3                    6   Closure            

And I need one row for each incident number with the smallest numbered approval step. So the result should look like this.

filtered query result

  Incident_No   Open_Approval_Step      Approval_ID     
 ------------- -------------------- ------------------- 
            1                    3   Tech               
            2                    1   Incident_Recorder  
            3                    4   Cust_Serv          

Edit This is what I came up with in the end

SELECT DISTINCT

MIN(OPEN_APPROVAL_STEP) OVER(PARTITION BY INCIDENT_NO ORDER BY OPEN_APPROVAL_STEP ASC) AS CUR_APP_STEP,

INCIDENT_NO

FROM T

Answer 1

You can use row_number():

select *
from (
    select 
        t.*,
        row_number() over(partition by incident_no order by open_approval_step) rn
    from mytable t
) t
where rn = 1

With just one extra column appart from the incident number and approval step, another option is aggregation and Oracle's keep syntax:

select 
    incident_no,
    min(open_approval_step) open_approval_step,
    min(approval_id) keep(dense_rank first order by open_approval_step) approval_id
from mytable
group by incident_no