title: Fetch relevant rows based on condition using Oracle 12C
body: <p>I am trying to query the below data set : </p>
  
  <p>Sample input : </p>
  
  <pre><code>create table t(sn varchar(100),easyrfqy varchar(10),ecaty int,ps varchar(10))
  insert into t values('abc','NO',0,'E'),('abc','YES',1,'N'),('abc','NO',0,'W'),
  ('def','NO',1,'E'),('def','NO',0,'X'),('xyz','NO',1,'X')
  </code></pre>
  
  <p>Expected out :</p>
  
  <pre><code>sn easyrfqy_update ecaty_update ps
  
  abc YES 1 Pref
  abc YES 1 Pref
  abc YES 1 Pref
  def NO  1 Pref
  def NO  1 Pref
  xyz NO  1 NP
  </code></pre>
  
  <p>Logic Based : for a particular sn if easyrfqy ='P' or 'E' or 'N' then Pref
  for a particular sn if it has a ecaty  1 then set 1 for all rows for that sn
  for a particular sn if it had easyrfqy 'YES' then set all rows as YES </p>
  
  <p>I tried this : </p>
  
  <pre><code>select * from
  (
  select *,row_number()over(partition by sn order by ecat desc) as rn from t
  )x where x.rn=1
  </code></pre>
  
  <p>Here is the SQl fiddle : <a href="http://www.sqlfiddle.com/#!6/e104d/1" rel="nofollow noreferrer">http://www.sqlfiddle.com/#!6/e104d/1</a></p>