title: Find the lastest date in oracle database
body: <p>I have a big trouble with the query in the oracle database ver.10. What I want is to find the last date <code>dateofstat</code> 
  I have try many solutions, but it works but it take too much time. 
  - Using rownum<br>
  - Using row_number()<br>
  - Using rank()  </p>
  
  <p>There are my tries:<br>
  <strong>1. rownum</strong></p>
  
  <pre><code>select dateofstat from (
  select  stat.dateofstat from dhg.statistics stat
  join (
    select distinct assetid from dhg.relatedasset
       where (`CONDITION1`)
    MINUS
    select distinct assetid from dhg.relatedasset
   where (`CONDITION2`)
  ) grs 
  on stat.assetid = grs.assetid
  order by stat.dateofstat desc
  )where rownum = 1
  </code></pre>
  
  <p>Explain plan:<br>
  <a href="https://i.stack.imgur.com/2pOC0.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/2pOC0.png" alt="enter image description here"></a></p>
  
  <p><strong>row_number()</strong>   </p>
  
  <pre><code>select dateofstat from (
    select stat.dateofstat,
      row_number() over (order by stat.dateofstat desc) rnumber
    from dhg.statistics stat
    join (
      select distinct assetid from dhg.relatedasset
      where (`CONDITION1`)
      MINUS
      select distinct assetid from dhg.relatedasset
      where (`CONDITION2`)
    ) grs 
    on stat.assetid = grs.assetid
  ) where rnumber = 1
  </code></pre>
  
  <p>Explain plan:<br>
  <a href="https://i.stack.imgur.com/LSplu.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/LSplu.png" alt="enter image description here"></a></p>
  
  <p><strong>rank()</strong>: This solution I did try but it gives repetitive rank number, because of it, I dont think that I should used this solution to find the top one. </p>
  
  <p>I dont know what should I do now, really need help. For testing, I use sqlplus on emacs, without <code>rownum</code> I take less than 3 second to get the first rows of this query.       </p>
  
  <pre><code>select  stat.dateofstat from dhg.statistics stat
  join (
    select distinct assetid from dhg.relatedasset
       where (`CONDITION1`)
    MINUS
    select distinct assetid from dhg.relatedasset
   where (`CONDITION2`)
  ) grs 
  on stat.assetid = grs.assetid
  order by stat.dateofstat desc
  </code></pre>
  
  <p>I wonder that I could have any workaround solution by this tweak. </p>
  
  <p><strong>UPDATE SOLUTION STATUS FROM @ANTON</strong>  </p>
  
  <pre><code>select  max(stat.dateofstat) from dhg.statistics stat
  join (
   select distinct assetid from dhg.relatedasset
       where relatedasset.assetid in (191759.0, 3.0, 5.0, 98.0, 99.0)
     or relatedasset.linkid in (3232.0, 1049.0, 1057.0, 1067.0, 102.0, 1032.0, 104.0, 105.0, 1051.0)
   MINUS
   select distinct assetid from dhg.relatedasset
       where relatedasset.assetid in (192106.0, 1014.0, 10302.0)
     or relatedasset.linkid in (210.0, 737.0, 126.0, 1053.0, 1054.0, 119.0, 3133.0)
   ) grs 
   on stat.assetid = grs.assetid
  </code></pre>
  
  <p>I must say, I did work for once, very weird behavior. In the first executing I take only 3 seconds to execute, but in second time, I take much much more time (I did not count). Here are explain plan: </p>
  
  <p><a href="https://i.stack.imgur.com/TuLjS.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/TuLjS.png" alt="enter image description here"></a></p>
  
  <p>In addition, I also did try the second solution which use <code>exist</code> and <code>not exists</code>, however, it does not work well.      </p>
  
  <pre><code>select  max(stat.dateofstat)
  from dhg.statistics stat
  where exists(select *
             from dhg.relatedasset rasset
             where stat.assetid = rasset.assetid
             and rasset.assetid in (191759.0, 3.0, 5.0, 98.0, 99.0)
             or rasset.linkid in (3232.0, 1049.0, 1057.0, 1067.0, 102.0, 1032.0, 104.0, 105.0, 1051.0)
              )
  and not exists (select *
             from dhg.relatedasset rasset2
             where stat.assetid = rasset2.assetid
             and rasset2.assetid in (192106.0, 1014.0, 10302.0)
             or rasset2.linkid in (210.0, 737.0, 126.0, 1053.0, 1054.0, 119.0, 3133.0)
              )
  </code></pre>
  
  <p>This query, the plan give me a painful result. 
  <a href="https://i.stack.imgur.com/LZHCL.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/LZHCL.png" alt="enter image description here"></a></p>