title: SQL where clause uses MAX Effective Date and NULL values
body: <p>I'm using a backend software to run SQL on Oracle. I need to fetch the latest row based on effective date.</p>
  
  <p>Here's an example table</p>
  
  <pre><code>Table "ACCOUNTS"
  Application | Sub_category | Account_Reference | Effective_date
  BC_ONLINE   | F            | ABC1234           | 01-JAN-13
  BC_ONLINE   | B            | ABC2345           | 01-JAN-13
  TE_Notice   | (NULL)       | 1234ABC           | 01-JAN-13
  TE_Notice   | (NULL)       | 9876DEF           | 01-APR-13
  </code></pre>
  
  <p>The software will pass in two parameters: Application and Sub_category, if I used the following SQL, on the following Application: <strong>BC_ONLINE</strong> and Sub_category: <strong>F</strong></p>
  
  <pre><code>select a.Account_Reference
  from   ACCOUNTS a
  where  a.Application = 'BC_ONLINE'
  and    a.Sub_category = 'F'
  and    a.Effective_date = (select max(b.Effective_date)
                             from   ACCOUNTS b
                             where  b.Effective_date &lt;= sysdate
                             and    b.Application = a.Application
                             and    b.Sub_category = a.Sub_category)
  </code></pre>
  
  <p>BUT should I use the following Application: <strong>TE_Notice</strong> and Sub_category: <strong>NULL</strong>
  and you cannot have this</p>
  
  <pre><code>and    a.Sub_category = null
  </code></pre>
  
  <p>since it must be</p>
  
  <pre><code>and    a.Sub_category is null
  </code></pre>
  
  <p>the trouble is the software is fixed, so what is the best way to use both value and null in where clause using max(effective_date)?</p>
  
  <p>I have tried this method, but it doesn't work</p>
  
  <pre><code>select a.Account_Reference
  from   ACCOUNTS a
  where  a.Application = 'TE_Notice'
  and    (a.Sub_category = '' or a.Sub_category is null)
  and    a.Effective_date = (select max(b.Effective_date)
                             from   ACCOUNTS b
                             where  b.Effective_date &lt;= sysdate
                             and    b.Application = a.Application
                             and    NVL(b.Sub_category,-1) = NVL(a.Sub_category,-1))
  </code></pre>
  
  <p>It simply returned the row with <strong>01-APR-13</strong>, but I should be getting the row with the Effective_date on <strong>01-JAN-13</strong>.</p>