Hive Query to get the last record inserted in the table

Question

 a                date             time         b
35573407        20170412        140930  310260453908912
35573407        20170412        140930  310260453908912
35573407        20170412        141054  310260453908912
35573407        20170412        025339  310260453908912
35573407        20170412        072918  310260453908912
35573407        20170412        091105  310260453908912
35573422        20170412        193605  310260453908912
35573407        20170412        121105  310260453908912
35573407        20170412        032439  310260453908912
35573407        20170412        032605  310260453908912

I am trying to figure out a hive query which get the last record inserted in the table with the b. The records need to be sorted by time column and get the last record. For suppose in the above records

35573422  20170412  193605  310260453908912

is the last record.

Answer 1

select  a,date,time,b

from   (select  *
               ,row_number() over 
                (
                    partition by    b
                    order by        date desc
                                   ,time desc
                ) as rn

        from    mytable
        ) t

where   t.rn = 1

---

+----------+----------+--------+-----------------+
|   a      |   date   |  time  |      b          |
+----------+----------+--------+-----------------+
| 35573422 | 20170412 | 193605 | 310260453908912 |
+----------+----------+--------+-----------------+