Query to filter duplicate rows for particular column values

I have below table with given values:-

create or Replace table CourseLibrary (
    Parent_code varchar(250),
    Parent_Name varchar(250),
    Course_Version varchar(250),
    Course_Locale varchar(250),
    Timestamp varchar(250)
);

Insert into Courselibrary values
('UXPUEMBR007', 'Back Injury Prevention', '1.0.004', 'en_US', '3'),
('UXPUEMBR007', 'Back Injury Prevention2', '1.0.002', 'en_US',  '4'),
('UXPUEMBR007', 'Back Injury Prevention1', '1.0.004', 'en_BR', '5'),
('UXPUEMBR007', 'Back Injury Prevention', '1.0.003', 'en_US', '2'),
('UXPUEMBR007', 'Back Injury Prevention', '1.0.004', 'en_US', '1'),
('UXPUEMBR008', 'House Safety', '1.0.006', 'en_US', '1'),
('UXPUEMBR008', 'House Safety', '1.0.005', 'en_US', '1'),
('UXPUEMBR008', 'House Safety', '1.0.006', 'en_US', '2');

Need query to filter Below ResultSet:

('UXPUEMBR007', 'Back Injury Prevention', '1.0.004', 'en_US', '3')
 ('UXPUEMBR008', 'House Safety', '1.0.006', 'en_US', '2')

I need a single row for a given Parent_code. If there are multiple Parent_code, then I have to look for highest Course_Version, if even Course_version’s are same, then I have to look for course_locale=’en_US’ only. And if even course_locale are same, then lastly I have to check for highest timestamp value.

Note: No primary key

Priority:-1st highest course_version, 2nd course_locale=’en_US’, 3rd timestamp

Answer

Solution 1:

WITH top_records AS (SELECT PARENT_CODE, PARENT_NAME, Course_Version, Course_Locale, Timestamp, RANK() OVER (PARTITION BY PARENT_CODE ORDER BY Course_Version DESC, Timestamp DESC) RN FROM Courselibrary WHERE Course_Locale = 'en_US') Select * from top_records where RN = 1;

Solution 2:

Select PARENT_CODE, PARENT_NAME, Course_Version, Course_Locale, Timestamp from (SELECT PARENT_CODE, PARENT_NAME, Course_Version, Course_Locale, Timestamp, RANK() OVER (PARTITION BY PARENT_CODE ORDER BY Course_Version DESC, Timestamp DESC) RN FROM Courselibrary WHERE Course_Locale = 'en_US') Where RN = 1;