I have a table where each ID is repeated 3 times. there is a date in front of each id in each row.
I want to select entire row for each ID where date is latest.
There are total 370 columns in this table i want all columns to get selected when i select that row.
Sample -
ID Name Date Marks .. .. ..
1 XY 4/3/2017 27
1 fv 4/3/2014 98
1 jk 4/3/2016 09
2 RF 4/12/2015 87
2 kk 4/3/2009 56
2 PP 4/3/2011 76
3 ee 4/3/2001 12
3 ppp 4/3/2003 09
3 lll 4/3/2011 23
The Answer should be
ID Name Date Marks .. .. ..
1 XY 4/3/2017 27
2 RF 4/12/2015 87
3 lll 4/3/2011 23
I am attempting as below -
select distinct ID,*,max(date) as maxdate from table
Also i am trying this in Hive . so not sure if some sql functions dont work in Hive
Thanks