title: complex JOIN Query, all DISTINCT from a table with latest DATE attribute from another TABLE
body: <p>I have table named Business Entities "BE" and another named Questionnaire "Q", there is another table named which holds their Relationship Name "QBE"</p>
  
  <p><strong>Table: BE</strong> </p>
  
  <pre><code>| BE_ID | Region | Name | Description | 
  | 1     | A      | kl   | ssss        |
  | 2     | A      | dds  | s saals     |
  | 3     | B      | wf   | ksd ks skd  |
  | 4     | B      | dsc  | sam sd  ;   |      
  </code></pre>
  
  <p><strong>Table: Q</strong></p>
  
  <pre><code>| Q_ID  | Name  | CreationDate  |
  | 1     | sce   | 2014-01-01    |
  | 2     | wcd   | 2014-02-01    |
  </code></pre>
  
  <p><strong>Table: QBE</strong></p>
  
  <pre><code>| QBE_ID | BE_ID | Q_ID |
  | 1      | 1     | 1    |
  | 2      | 1     | 2    |
  | 3      | 2     | 2    |
  </code></pre>
  
  <p>now I want to select all the distinct Region from <strong>BE</strong> where CreationDate of <strong>Q</strong> is latest. </p>
  
  <p>I am working on these lines. First JOIN </p>
  
  <pre><code>| QBE_ID | BE_ID | Q_ID | Region | Name | Name  | CreationDate  | 
  | 1      | 1     | 1    | A      | kl   | sce   | 2014-01-01    |
  | 2      | 1     | 2    | A      | dds  | sce   | 2014-02-01    |
  | 3      | 2     | 2    | B      | wf   | wcd   | 2014-02-01    |
  </code></pre>
  
  <p>then from this I want to select </p>
  
  <pre><code>| QBE_ID | BE_ID | Q_ID | Region | Name | Name  | CreationDate  | 
  | 2      | 1     | 2    | A      | dds  | sce   | 2014-02-01    |
  | 3      | 2     | 2    | B      | wf   | wcd   | 2014-02-01    |
  </code></pre>
  
  <p>for all Regions "A" Q_ID "2" had the Questionaire with latest CreationDate so, then for All Regions "B" then for all others.</p>
  
  <p>So far here is what I have been doing, don't read it its complex just tell me how to do it. </p>
  
  <pre><code>SELECT BE.NAME00, QUES.ANSWER, QUES.DESCRIPTION, QT.CREATION_DATE
    FROM OPAGES.RV_ENTITY AS BE,
        OPAGES.RT__ENTITY_QUESTIONNAIRE AS EN_QT,
        OPAGES.RV_QUESTIONNAIRE AS QT,
        OPAGES.RT__QUESTIONNAIRE_QSECTION AS QT_SEC,
        OPAGES.RT__QSECTION_QUEST AS SEC_QUES, OPAGES.RT_QUEST AS QUES
    WHERE BE.ENTITY_ID = EN_QT.ENTITY_ID AND EN_QT.QUESTIONNAIRE_ID =
  QT.QUESTIONNAIRE_ID
      AND QT.QUESTIONNAIRE_ID = QT_SEC.QUESTIONNAIRE_ID
      AND QT_SEC.QSECTION_ID = SEC_QUES.QSECTION_ID
      AND SEC_QUES.QUEST_ID = QUES.QUEST_ID
      AND QT.QUESTIONNAIRE_ID= (SELECT QT_SEC.QUESTIONNAIRE_ID
    FROM OPAGES.RT__QUESTIONNAIRE_QSECTION AS QT_SEC,
        OPAGES.RV_QUESTIONNAIRE AS QT
    WHERE QT.QUESTIONNAIRE_ID = QT_SEC.QUESTIONNAIRE_ID
    GROUP BY QT_SEC.QUESTIONNAIRE_ID, QT.CREATION_DATE
    ORDER BY QT.CREATION_DATE DESC
    FETCH FIRST ROW ONLY
  )
    GROUP BY QT.CREATION_DATE, QUES.DESCRIPTION, QUES.ANSWER, BE.NAME00
  </code></pre>
  
  <p>I am thinking something like </p>
  
  <pre><code>SELECT * 
  FROM MyTable
  WHERE (User, Date) IN
    ( SELECT User, MAX(Date) FROM MyTable GROUP BY User)
  </code></pre>
  
  <p>just cant materialize it</p>