Site icon BaJa Traveler

Hive QL retrieve max value

I am trying to use Hive QL to retrieve data from the database. I have 2 columns start_time having the starting time and time_stamp which is recorded for each of the works done starting from ‘Start_time’

JOB         start_time              work_done             time_stamp
-------------------------------------------------------------------------
JOB_A     2021/12/29 11:00:00       work_A            2021/12/29 11:00:00
JOB_A     2021/12/29 11:00:00       work_A            2021/12/29 11:20:00
JOB_A     2021/12/29 11:00:00       work_B            2021/12/29 11:45:00
JOB_B     2021/12/29 11:00:00       work_A            2021/12/29 12:00:00
JOB_B     2021/12/29 11:00:00       work_A            2021/12/29 12:15:00
JOB_B     2021/12/29 11:00:00       work_B            2021/12/29 12:30:00

What I want is the start_time of JOB_A and the time_stamp where JOB_A and JOB_B ends

JOB         start_time                     time_stamp
------------------------------------------------------------
JOB_A     2021/12/29 11:00:00            2021/12/29 11:45:00
JOB_B     2021/12/29 11:00:00            2021/12/29 12:30:00

I tried to use

select 
JOB,
start_time,
max(time_stamp)
from table_1

I am still getting the same table as my output

Exit mobile version