title: hadoop hive using row_number()
body: <p>I have a dataset with many duplicating IDs. I just want to do a row_number() and take the first. If i have table1 left join with table2 and only take table2.rownumber=1, it works. but if i do a standalone without table join, it doesn't. I have the following code:</p>
  
  <p>SELECT 
  ID,
  NAME,
  NRIC,
  ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) as RNK
  FROM TABLE1
  WHERE RNK=1;</p>
  
  <p>The error message show that RNK is not a valid table column or alias etc. </p>
  
  <p>Any help would be greatly appreciated. Thanks.</p>