title: SQL QUALIFY equivalent HIVE query
body: <p>I'm trying to create a <code>HIVE query</code> from an <code>Oracle SQL query</code>. Essentially I want to select the first record, sorted <code>descending</code> by <code>UPDATED_TM, DATETIME, ID_NUM</code>.</p>
  
  <pre><code>SELECT 
    tbl1.NUM AS ID,
    tbl1.UNIT AS UNIT,  
    tbl2.VALUE AS VALUE,
    tbl1.CONTACT AS CONTACT_NAME,
    'FILE' AS SOURCE,
    CURDATE() AS DATE
  FROM
    DB1.TBL1 tbl1
    LEFT JOIN DB1.TBL2 tbl2 ON tbl1.USR_ID = tbl2.USR_ID
  WHERE
    tbl1.UNIT IS NOT NULL
    AND tbl1.TYPE = 'Generic'
  QUALIFY 
    ROW_NUMBER() OVER (PARTITION BY tbl1.ROW_ID ORDER BY tbl1.UPDATED_TM DESC, tbl1.DATETIME DESC, tbl1.ID_NUM DESC) = 1
  </code></pre>
  
  <p>And my attempt at an equivalent <code>Hive query</code> (but also sql-compatible):</p>
  
  <pre><code>SELECT 
    tbl1.NUM AS ID,
    tbl1.UNIT AS UNIT,  
    tbl2.VALUE AS VALUE,
    tbl1.CONTACT AS CONTACT_NAME,
    'FILE' AS SOURCE,
    CURDATE() AS DATE
  FROM (
    SELECT 
      USR_ID, TYPE, NUM, UNIT, ROW_NUMBER() OVER (PARTITION BY tbl1.ROW_ID ORDER BY tbl1.UPDATED_TM DESC, tbl1.DATETIME DESC, tbl1.ID_NUM DESC) AS RNUM
    FROM 
      DB1.TBL1
    ) tbl1
    LEFT JOIN DB1.TBL2 tbl2 ON tbl1.USR_ID = tbl2.USR_ID
  WHERE
    tbl1.RNUM = 1
    AND tbl1.UNIT IS NOT NULL
    AND tbl1.TYPE = 'Generic'
  </code></pre>
  
  <p>Does that seem correct? Is there any way I can optimize the query? The tables I'm working with are quite large and I would like to make this as efficient as possible.</p>
  
  <p>Thanks.</p>