CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Azat Usmanov
Azat Usmanov

Reputation: 103

get only row that meet condition if such row exist and if not get the row that meet another condition

this sounds like a simple question but I just cant find the right way. given the simplified table 

with 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
)
)
select * from t -- where clause  should be here

Input data

for every row is_restore guaranteed to be 1 or 0. if table has a row where is_restore=1 then select ordernumber,orderdate of that row and nothing else. If a table does not have a row where is_restore=1 then select ordernumber,orderdate of the row where rn=1(row where rn=1 is guaranteed to exist in a table) Given the requirements above what do I need to put in where clause to get the following?

desirable output

Upvotes: 1

Views: 69

Answers (2)

Alex Sham
Alex Sham

Reputation: 468

Here is sql, that doesn't use window functions, maybe it will be useful for those, whose databases don't support OVER ( ... ) or when there are indexed fields, on which query is based.

SELECT
* 
FROM t
WHERE t.is_restore = 1 
OR (
 NOT EXISTS (SELECT 1 FROM t WHERE t.is_restore = 1)
 AND t.rn = 1
)

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 176324

You could use ROW_NUMBER:

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);

-------------------

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;

db<>fiddle demo

Upvotes: 1

Related Questions