hadoop hive using row_number()

You have to use a subquery or CTE to refer to a column alias for filtering:

SELECT ID, NAME, NRIC, RNKFROM (SELECT t1.*, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) as RNK      FROM TABLE1     ) t1WHERE RNK = 1;

This is true of all column aliases, even though defined by window functions.

Given:

create table dupes (id string,democode string,extract_timestamp string);

And:

insert into dupes (id, democode,extract_timestamp) values ('1','code','2020'),('2','code2','2020'),('2','code22','2021'),('3','code3','2020'),('3','code33','2021'),('3','code333','2012');

When:

SELECT id,democode,extract_timestampFROM (  SELECT id,democode,extract_timestamp,   ROW_NUMBER() OVER (PARTITION BY id ORDER BY extract_timestamp DESC) AS row_num  FROM dupes ) t1WHERE row_num = 1;

Then:

+-----+-----------+--------------------+--+| id  | democode  | extract_timestamp  |+-----+-----------+--------------------+--+| 1   | code      | 2020               || 2   | code22    | 2021               || 3   | code33    | 2021               |+-----+-----------+--------------------+--+

Note that often tables are partitioned and that we might want to deduplicate within each partition. In which case we would add the partition key(s) into the OVER statement. For example if the table was partition by report_date DATE then we might use:

ROW_NUMBER() OVER (PARTITION BY id, report_date ORDER BY extract_timestamp DESC) AS row_num