title: Oracle 11g: Sub query returning multiple rows
body: <p>I have two tables </p>
  
  <p>Table-1: <strong>Account_Table</strong></p>
  
  <pre><code>Account_Num | Serial_Num | Transaction_Date | Balance | Frequency 
    ACC1           001           Date1            0          f1
    ACC1           002           Date2            1          f2
    ACC1           003           Date3            2          f3
  </code></pre>
  
  <p>Table-2: <strong>Bill_Table</strong></p>
  
  <pre><code>Account_Num | Serial_Num | Version_Num | Bill_Date 
    ACC1           001            1          Date1 (say)
    ACC1           002            1          Date2
    ACC1           001            1          Date3  
  </code></pre>
  
  <p>I need to get the <code>Bill_Date</code> from <code>Bill_Table</code> by joining <code>Account_Num</code> and <code>Serial_Num</code> of both <code>Account_Table</code> and <code>Bill_Table</code>.</p>
  
  <p>If the Bill_Table has more than one row matches with Account_Num, Serial_Num. This is the case for <code>ACC1, 001 of Account_Table</code> matches with two rows of <code>ACC1, 001 of Bill_Table</code> then I need to get the maximum version number of the respective Account_Num, Serial_Num combination and return that row's Bill_Date. </p>
  
  <p>This is a special case where the version number is same for both ACC1, 001 combination, so I need to get the <code>TOP 1</code> (that is ROWNUM = 1)</p>
  
  <p>Example: Account_Table has 3 rows. Now my output should be </p>
  
  <p><strong>Expected Result:</strong></p>
  
  <pre><code>Account_Num | Serial_Num | Bill_Date 
    ACC1           001         Date
    ACC1           002         Date
    ACC1           003         null
  </code></pre>
  
  <p>Query which I have tried is,</p>
  
  <pre><code>SELECT 
            A.ACCOUNT_NUM,  
            A.SERIAL_NUM, 
            B.BILL_DATE 
     FROM ACCOUNT_TABLE A, BILL_TABLE B WHERE 
           A.ACCOUNT_NUM = 'ACC1' AND 
           B.ACCOUNT_NUM = 'ACC1' AND 
           A.SERIAL_NUM = B.SERIAL_NUM AND    
           B.VERSION_NUM = (SELECT MAX (BIV.VERSION_NUM) FROM BILL_TABLE BIV
                                      WHERE BIV.ACCOUNT_NUM = 'ACC1' 
                                      AND BIV.SERIAL_NUM = B.SERIAL_NUM
                          );
  </code></pre>
  
  <p>But this query results in a table which does not show my <code>ACC1, 003</code> combination and <code>ACC1, 001</code> two times.</p>
  
  <p><strong>OUTPUT:</strong></p>
  
  <pre><code>ACCOUNT_NUM | SERIAL_NUM | BILL_DATE 
     ACC1          001         Date
     ACC1          001         Date
     ACC2          002         Date
  </code></pre>
  
  <p>Any help will be appreciated.</p>
  
  <p>P.S: One thing which I have done wrong is I am comparing only the rows that are matching both the serial numbers <code>A.SERIAL_NUM = B.SERIAL_NUM</code>. But if I remove this line then I am getting more rows.</p>