title: Select Top row in Oracle Sql Query
body: <p>Hi I'm new to Oracle and unable to get the Top row using my query below </p>
  
  <pre><code>SELECT per.person_no, 
         perbus.person_no, 
         perbus.edit_dt, 
         perbus.org_name, 
         perbus.FIRST_NAME 
  FROM users.persons per
      LEFT OUTER JOIN (
          SELECT ASSOC.edit_dt,PER_CONTACTS.PERSON_NO, 
                 PER_CONTACTS.PERSON_ID AS PER_ID,
                 PER.EXTERNAL_PERSON_ID AS EXT_PER_ID,
                 PER_CONTACTS.LNAME||', '||PER_CONTACTS.FNAME AS NAME, 
                 PER_CONTACTS.FNAME AS FIRST_NAME, 
                 PER_CONTACTS.MNAME AS MIDDLE_NAME, 
                 PER_CONTACTS.LNAME AS LAST_NAME,
                 ORG.ORG_NAME AS ORG_NAME,
                 T_ORG.ORG_ID AS ORG_ID,
                 T_ORG.ORG_DISPLAY_ID AS EXT_ORG_ID,
                 rownum AS rn
          FROM USERS.PER_CONTACTS PER_CONTACTS
              LEFT OUTER JOIN USERS.PERSONS PER ON PER.PERSON_ID = PER_CONTACTS.PERSON_ID
              LEFT OUTER JOIN USERS.ASSOC ASSOC ON ASSOC.PERSON_ID = PER.PERSON_ID
              LEFT OUTER JOIN USERS.T_ORG T_ORG ON T_ORG.T_ORG_ID = ASSOC.T_ORG_ID
              LEFT OUTER JOIN USERS.ORG ORG ON ORG.ORG_ID = T_ORG.ORG_ID
          WHERE PER_CONTACTS.CONTACT_ROLE_LOV = 'EMPLOYEE' AND PER_CONTACTS.PERSON_NO IN (SELECT PERSON_NO FROM USERS.QA_APPROVED)
          ORDER BY ASSOC.edit_dt DESC
      ) 
      perbus ON perbus.PERSON_NO = PER.PERSON_NO and Rownum = 1
  WHERE PER.PERSON_NO IN (SELECT PERSON_NO FROM USERS.QA_APPROVED)
  </code></pre>
  
  <p>I've tried using <strong>rownum = 1</strong> and <strong>rownum &lt; 2</strong>, both however seem to have fetch two rows (image below). 
  <a href="https://i.stack.imgur.com/3rByZ.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/3rByZ.jpg" alt="enter image description here"></a></p>
  
  <p>If you see I have John Doe same person with same person ID , but works for two different companies. I would only like my query to show the <strong>latest</strong> <strong>edited</strong> one which in this case is the <strong>one</strong> with <strong>Google</strong> record.
  But for some reason the <strong>rownum</strong> is not working.</p>
  
  <p>I also tried - </p>
  
  <pre><code>SELECT per.person_no, 
         perbus.person_no, 
         perbus.edit_dt, 
         perbus.org_name, 
         perbus.FIRST_NAME 
  FROM users.persons per
      LEFT OUTER JOIN (
          SELECT ASSOC.edit_dt,PER_CONTACTS.PERSON_NO, 
                 PER_CONTACTS.PERSON_ID AS PER_ID,
                 PER.EXTERNAL_PERSON_ID AS EXT_PER_ID,
                 PER_CONTACTS.LNAME||', '||PER_CONTACTS.FNAME AS NAME, 
                 PER_CONTACTS.FNAME AS FIRST_NAME, 
                 PER_CONTACTS.MNAME AS MIDDLE_NAME, 
                 PER_CONTACTS.LNAME AS LAST_NAME,
                 ORG.ORG_NAME AS ORG_NAME,
                 T_ORG.ORG_ID AS ORG_ID,
                 T_ORG.ORG_DISPLAY_ID AS EXT_ORG_ID,
  ROW_NUMBER() OVER (ORDER BY ASSOC.edit_dt DESC) AS  RN1,
  ROW_NUMBER() OVER(PARTITION BY PER_CONTACTS.PERSON_ID ORDER BY ASSOC.edit_dt DESC) RN2
          FROM USERS.PER_CONTACTS PER_CONTACTS
              LEFT OUTER JOIN USERS.PERSONS PER ON PER.PERSON_ID = PER_CONTACTS.PERSON_ID
              LEFT OUTER JOIN USERS.ASSOC ASSOC ON ASSOC.PERSON_ID = PER.PERSON_ID
              LEFT OUTER JOIN USERS.T_ORG T_ORG ON T_ORG.T_ORG_ID = ASSOC.T_ORG_ID
              LEFT OUTER JOIN USERS.ORG ORG ON ORG.ORG_ID = T_ORG.ORG_ID
          WHERE PER_CONTACTS.CONTACT_ROLE_LOV = 'EMPLOYEE' AND PER_CONTACTS.PERSON_NO IN (SELECT PERSON_NO FROM USERS.QA_APPROVED)
          ORDER BY ASSOC.edit_dt DESC
      ) 
      perbus ON perbus.PERSON_NO = PER.PERSON_NO and rownum = 1
  WHERE PER.PERSON_NO IN (SELECT PERSON_NO FROM USERS.QA_APPROVED)
  </code></pre>
  
  <p>Which would still give the output as below</p>
  
  <p><a href="https://i.stack.imgur.com/NToGt.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/NToGt.jpg" alt="enter image description here"></a></p>
  
  <p>I need to show the top row only but i get two rows as output.
  I need to make sure the left outer join spits out a single row - any thoughts ?
  i also tried maxdate option with no effect. Do you think i should consider CTE ?</p>