CODEWITHSUNDEEP
sqloracle-database
Jack
Jack

Reputation: 1

Select first value and its related rows out of two values based on column condition otherwise select second value and its related rows

I have the Oracle SQL shown below 

select * 
from a
where a.description in ('Rebook All (1)', 'Created stock identifier') 
    and a.date between '03/18/2017 00:00:01' AND '03/25/2017 00:00:00'
    and a.unit = '38240007294677'

Now result can have both description multiple times like below for a Unit number or can have 1 of the description. When it has both description for a Unit number, I need to select only 'Rebook all (1)' (this description can be there many times/many rows as shows below in results) description not 'Created stock identifier' and its related details from table a. When it has only 1 description out of these 2 then if 'Rebook All (1)' is not there, select 'Created stock identifier' and vice versa.

How do I select it?

  Date                                         Description

3/20/2017 10:11:12.769000 AM Created stock identifier
3/20/2017 10:10:47.775000 AM Created stock identifier
3/20/2017 10:10:47.782000 AM Created stock identifier
3/20/2017 8:14:41.696000 PM Rebook all (1)
3/20/2017 8:14:41.885000 PM Rebook all (1)
3/20/2017 8:13:51.622000 PM Rebook all (1)

I need results like below when there are both description ('Rebook All (1)', 'Created stock identifier') for a Unit number then should get only ('Rebook All (1)') and all its rows, BUT when have only 1 description out of ('Rebook All (1)', 'Created stock identifier') for a Unit number then get that description -

When have both description ('Rebook All (1)', 'Created stock identifier') for a unit number then get only ('Rebook All (1)') like below -

3/20/2017 8:14:41.696000 PM Rebook all (1)
3/20/2017 8:14:41.885000 PM Rebook all (1)
3/20/2017 8:13:51.622000 PM Rebook all (1)

If have only 1 description 'Created stock identifier' for a unit number then get that 'Created stock identifier' only - 

3/20/2017 10:11:12.769000 AM Created stock identifier
3/20/2017 10:10:47.775000 AM Created stock identifier
3/20/2017 10:10:47.782000 AM Created stock identifier

Upvotes: 0

Views: 154

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95080

As of Oracle 12c:

select a.*
from a
where a.unit = '38240007294677' 
and a.description in ('Rebook All (1)', 'Created stock identifier') 
and a.date >= date '2017-01-18' 
and a.date <  date '2017-03-25'
order by case when a.description = 'Rebook All (1)' then 1 else 2 end
fetch first row with ties;

Upvotes: 0

Pons
Pons

Reputation: 1111

This is one way of achieving your requirement. I used RANK() OVER window function to rank the unit records based on their description in the DESC order.

SELECT * 
FROM (
  SELECT UNIT
    , DESCRIPTION
    , RANK() OVER (PARTITION BY UNIT ORDER BY DESCRIPTION DESC) RN
  FROM MY_TABLE A
  WHERE A.DESCRIPTION IN ('Rebook all (1)'
                          , 'Created stock identifier')
    AND A.DATE BETWEEN '03/18/2017 00:00:01' AND '03/25/2017 00:00:00'
    --AND A.UNIT = '38240007294677'
)
WHERE RN = 1

Upvotes: 2

Related Questions