Filtering a dataset based on condition SQL Oracle

Question

I have the following input and expected output I am looking for. Basically I would like to filter request_id only when the type = crossborder` and then show the units. I think I would need to use some Min and Max but I am not sure how to use it.

Input

request_id    type            unit_count
A11           local           10
A11           crossborder     5
B11           local           15    
C11           crossborder     25

Output

request_id    type            unit_count
C11           crossborder           25

Answer 1

I think what you can use here in ranking. You can rank based on anything in the rows and order by the value to leave the highest value on top, that value will get the ranking # 1, all you have to do later is to filter for all the rankings #1.

This little demo will show what I am talking about:

with data as (
select 1 as id, 'cross' as type, 27 as unit from dual union
select 1 as id, 'cross' as type, 23 as unit from dual union
select 1 as id, 'cross' as type, 2 as unit from dual union
select 3 as id, 'cross' as type, 25 as unit from dual union
select 2 as id, 'cross' as type, 23 as unit from dual union
select 5 as id, 'cross' as type, 2 as unit from dual)
select id, type,unit, rank() over ( order by unit desc) ranking from data where type='cross'

Output:

Answer 2

As per my understanding of the question, You don't need MAX or MIN but simply an aggregation -

SELECT request_id, type, unit_count
  FROM (SELECT request_id, type, unit_count, 
               COUNT(*) OVER (PARTITION BY request_id) CNT
          FROM YOUR_TABLE)
 WHERE CNT = 1
   AND type = 'crossborder';