By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE t
AS
select ordernumber, orderdate,
case when ordertype in (5,21) then 1 else 0 end is_restore, ordertype,
row_number() over(order by orderdate) rn
from (
select to_date('29.08.08','DD.MM.YY') orderdate,'313' ordernumber,
1 as ordertype
from dual union all
select to_date('13.03.15','DD.MM.YY') orderdate, '90/4/2' ordernumber,
5 as ordertype
from dual);
2 rows affected
with cte as (
select t.*, ROW_NUMBER() OVER(/*PARTITION BY ...*/ ORDER BY is_restore DESC, rn) AS rnk
from t
)
SELECT *
FROM cte
WHERE rnk = 1;
| ORDERNUMBER | ORDERDATE | IS_RESTORE | ORDERTYPE | RN | RNK |
|---|---|---|---|---|---|
| 90/4/2 | 13-MAR-15 | 1 | 5 | 2 | 1 |