Home » SQL & PL/SQL » SQL & PL/SQL » deleteing duplicates
deleteing duplicates [message #199949] Fri, 27 October 2006 01:54 Go to next message
ashyour
Messages: 2
Registered: October 2006
Location: Lucknow India
Junior Member
I have a table emphistory containing fields(empno,name,salary,dateofrecord)
it conatains duplicate records with empno,name but different salary and dateofrecord.
the pair salary and dateofrecord represents salary on the specified date.
Now I want to remove old records of the employees and I want to have the latest record of each employee.
please suggest the query.
Re: deleteing duplicates [message #199952 is a reply to message #199949] Fri, 27 October 2006 02:04 Go to previous messageGo to next message
sundarsoft
Messages: 15
Registered: October 2006
Junior Member
Hi

Use Partion by clause

select * from (
select empId,ename,row_number()over(partition ny empid,ename order by data desc) rnk from emp)
where rnk = 1

these recordds are latest ones..
and now you can delete records where not in (1)

Sundar
Re: deleteing duplicates [message #199965 is a reply to message #199952] Fri, 27 October 2006 03:49 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Have you searched the board yet?
Have you looked at the FAQ pages?

MHE
Re: deleteing duplicates [message #199969 is a reply to message #199949] Fri, 27 October 2006 04:10 Go to previous messageGo to next message
sundarsoft
Messages: 15
Registered: October 2006
Junior Member
I cannot understand wht u said sir?

Is i am wrong in answering this Q?

Plz tell me i will clarify my mistakes......
Re: deleteing duplicates [message #200021 is a reply to message #199969] Fri, 27 October 2006 07:44 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Nope, you did nothing wrong, sundarsoft, but if ashyour had bothered to read the sticky (tips&tricks) and if he would have followed the guidelines he would have found that the deletion of duplicates is explained here on the FAQ pages and in several posts of the past. That is why there is a search button. That's all Wink

MHE
Previous Topic: How do I extract last element of a string?
Next Topic: bulk analytic update
Goto Forum:
  


Current Time: Wed Dec 07 22:29:21 CST 2016

Total time taken to generate the page: 0.17514 seconds