How to use hierarchical query in SQL

Question

I want to use the following query as a hierarchical query without any duplication in job Id and job description even though the time stamp varies

For example, there should be only one job_id, job_description showing without any duplication and by using min and max function in the following fields exec_start_time and exec_end_time. The sol_id from tbaadm.bjm table should be mapped with set_id from table tbaadm.sst.

Original Query:

Select  Sol_Id,
        Job_Id,
        Job_Desc,
        To_Char(Exec_Start_Time,'HH:MM:SS'),
        To_char(Exec_End_Time,'HH:MM:SS'),
        Job_Status
From    Tbaadm.Bjm
Where   Job_Exec_Date = '21-10-2016'
And     Job_Desc Like 'SDP%'
Order By Job_Id;

Then I got stuck with this

Select  Sol_Id,
        Job_Id,
        Job_Desc,
        Min(V_Exec_Start_Time),
        Max(V_Exec_End_Time),
        Job_Status
From    (Select Sol_Id,
                Job_Id,
                Job_Desc,
                (To_Char(Exec_Start_Time,'HH:MM:SS')) As v_exec_start_time,
                (To_Char(Exec_End_Time,'HH:MM:SS')) As v_exec_end_time,

                Job_Status
         From Tbaadm.Bjm
         Where Sol_Id In (Select Sol_Id From Tbaadm.Sst Where Set_Id = '100')
         And   Job_Exec_Date = '21-OCT-2016'
         And   Job_Desc Like 'SDP%')
Group By Sol_Id,Job_Id,Job_Desc,V_Exec_Start_Time,V_Exec_End_Time,Job_Status
Order By Job_Id;

Answer 1

If u just want one line for each job then it is not called hierarchical because hierarchy means there is some parent child relationship and you want data of a tree kind of fashion in tabular format.

Here you can simply use group by function to get one line for each job having "Min and Max function" applied on required fields (Exec_Start_Time,Exec_End_Time).

Thanks, Jayati