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