locked
How to get the values from multiple rows after comparing certain columns?? RRS feed

  • Question

  • User-1797368610 posted

    Hi,

    i am trying to get the values from multiple rows from oracle table. query condition would be something like if project_num, building_id, Physical_inspect_date and phy_cond are same then compare the values from Repair_Time column and get the maximum value and return the same row values for Deficiency_code, life_safety, on_prior_rep. please have a look at below screen shot to have idea. in that case i would like to get values from first row as repair time is greater than second row. Values should return C, *, Y, 4.

    Project_num Building_Id Physical_Inspect_Date Phy_Cond Deficiency_Code Life_Safety On_Prior_Rep Repair_Time
    600002 2559 6/8/2018 Exterior C * Y 4
    600002 2559 6/8/2018 Exterior M N 2

    Thanks in Advanced!

    Wednesday, May 9, 2018 2:30 PM

Answers

  • User269602965 posted

    Use oracle analytics to partition and sort within partition

    Your problem will be when two rows have the exact same repair time, it will only keep 1 row


    SELECT
     project_num,
     building_id,
     Physical_inspect_date,
     phy_cond,
     Repair_Time,
     Deficiency_code,
     life_safety,
     on_prior_rep
    FROM
     (
     SELECT
      project_num,
      building_id,
      Physical_inspect_date,
      phy_cond,
      Repair_Time,
      Deficiency_code,
      life_safety,
      on_prior_rep,
      ROW_NUMBER() OVER (PARTITION BY project_num, building_id, Physical_inspect_date, phy_cond
                ORDER BY project_num, building_id, Physical_inspect_date, phy_cond, Repair_Time DESC) AS ROW_NUM
     FROM
      {schema and table name}
     )
    WHERE ROWNUM = 1
    /

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 9, 2018 4:41 PM