There are duplicate records in table, we need to select only the latest records as per date using gr-CodePudding

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;```

CodePudding user response：

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