How to get records based on status or recent updated timestamp?

I have following Vehicle table with given records.

Create Table Vehicle (Vehicle_Type varchar(50),Car_Number number,Car_Type varchar(10),Car_Status char(1),Car_Repaired_Date Date);
Insert into Vehicle(Vehicle_Type,Car_Number,Car_Type,Car_Status,Car_Repaired_Date)values('CAR',123456,'SUV','Y','16-MAR-20 06.49.05');
Insert into Vehicle(Vehicle_Type,Car_Number,Car_Type,Car_Status,Car_Repaired_Date)values('CAR',234567,'SUV','N','16-MAR-20 06.49.05');
Insert into Vehicle(Vehicle_Type,Car_Number,Car_Type,Car_Status,Car_Repaired_Date)values('CAR',334567,'SEDAN','N','16-MAR-20 06.49.05');
Insert into Vehicle(Vehicle_Type,Car_Number,Car_Type,Car_Status,Car_Repaired_Date)values('CAR',4567890,'SEDAN','N','14-MAR-20 06.49.05');
commit;

I am trying get records for Vehicle_Type='CAR' where CAR_STATUS='Y' with distinct CAR_TYPE.If any of the CAR_TYPE's CAR_STATUS='N' then get the recently updated CAR for that CAR_TYPE.

INPUT:

INPUT

Expected OUTPUT;

Output

Thanks in advance.

1 answer

  • answered 2020-04-05 21:43 Gordon Linoff

    If I understand correctly, you can use row_number():

    select v.*
    from (select v.*,
                 row_number() over (partition by car_type
                                    order by car_status desc,   -- 'Y' before 'N'
                                             car_repaired_date desc
                                   ) as seqnum
          from vehicle v
          where vehicle_type = 'CAR'
         ) v
    where seqnum = 1;
    

    This returns one row per car_type (based on the partition by clause). That row is either the most recent row with a 'Y' or if there are no 'Y's, then then most recent with an 'N'.