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
This question has been asked before. Please see this question.
Using the accepted answer and adapting it to your problem you get:
SELECT tt.*
FROM myTable tt
INNER JOIN
(SELECT ID, MAX(Date) AS MaxDateTime
FROM myTable
GROUP BY ID) groupedtt
ON tt.ID = groupedtt.ID
AND tt.Date = groupedtt.MaxDateTime