select records with maximum date column grouped by primary keys in Hive sql

0
votes

I have a table that looks like follows

primary_key  act_date  C1 C2 C3 C4 ...C50
K1           01-12-18
K1           03-12-18
K1           30-12-18
K2           16-11-18
K3           null
K4           null          
K4           15-11-18

I want to select all columns and rows such that only rows corresponding to max(act_date) grouped by primary_key is selected. If there are two records with null as act_date and value, select the row which is not null in act_date column.

The final o/p for the above table must look like follows

primary_key  act_date  C1 C2 C3 C4 ...C50
K1           30-12-18
K2           16-11-18
K3           null         
K4           15-11-18

I tried the following but its asking me to group by other columns in the table as well which will again o/p multiple rows per primary_key which is not what I need.

select a.* from
(select a.*,
       max(act_date) over() as max_act_date 
  from table a) a
 where (a.act_date = a.max_act_date)
group by a.primary_key;

Appreciate any feedback/input

2
sqlhivehiveql

2 Answers

1
votes

Just so its clear, primary_key implies a unique field. In your example primary_key is just a column_name.

Here is one way to do it

select * from(
select *
       ,row_number() over(partition by t.primary_key order by act_date desc) rnk
 from table
  )x
where x.rnk=1
0
votes

Another way of getting the max value.

select primary_key, max(act_date) as max_act_date from table group by primary_key ;