There are duplicate records in table, we need to select only the latest records as per date using group by or window function in sql

There are duplicate records in table, we need to select only the latest records as per date using group by or window function in sql

| emp_id | emp_name | department| create_date
+--------------------------------------------+
|   1    |  Nilesh  |    10     |  1-JAN-22  |
|   1    |  Nilesh  |    11     |  5-JAN-22  |
|   2    |  Rohit   |    12     |  1-JAN-22  | 
|   2    |  Rohit   |    13     |  5-JAN-22  |

code:

select * from (
    select emp_id, emp_name, create_date, department, row_number() over
        (partition by date(create_date) 
         order by create_date desc) as row_num 
    from emp1) 
where row_num = 1
order by create_date;```

Answer

You can simply use a GROUP BY like so

SELECT 
    emp_id, emp_name, department, MAX(create_date)
FROM 
    emp1
GROUP BY 
    emp_id, emp_name, department

If you want to use ROW_NUMBER you have to PARTITION BY the id

SELECT 
    emp_id, emp_name, department, create_date
FROM 
(
    SELECT 
        emp_id, emp_name, department, create_date, ROW_NUMBER () OVER (partition by emp_id order by create_date desc) as num
    FROM 
        emp1
) sub_query
where num = 1