Home » SQL & PL/SQL » SQL & PL/SQL » duplicate problem fix (oracle 10g)
duplicate problem fix [message #419706] Tue, 25 August 2009 04:59 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi iam having a table like x

where record are like this

a b c
100.81 1567 1553
100.81 1772 1772
100.81 1772 1831
100.81 1782 1774

there are so many records in it like this iam giving an example
i want a query where i can delete a record if a and b are repeating twice then we have to consider the maximun one of c.

that means out of this
100.81 1772 1772
100.81 1772 1831

i want only 2 row to be there.

please give a delete query for this.
Re: duplicate problem fix [message #419707 is a reply to message #419706] Tue, 25 August 2009 05:04 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
More than 300 posts!!! and still unable to post properly.
First read guidelines to post your question.

And please tell us that what have you tried before posting question to this forum.

regards,
Delna
Re: duplicate problem fix [message #419724 is a reply to message #419706] Tue, 25 August 2009 05:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you try to do something before posting?
Obviously one thing you did not try is to follow forum guide.

Regards
Michel
Re: duplicate problem fix [message #419725 is a reply to message #419724] Tue, 25 August 2009 06:03 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi,
for me its working with mssql and i need it in oracle too.

DELETE D
FROM
(
	SELECT *
		,ROW_NUMBER() OVER (PARTITION BY problem_code, patient_id ORDER BY PROBLEM_ID DESC) AS RowNum
	FROM EMRPATIENTPROBLEMS
) D
WHERE RowNum > 1



iam trying it in oracle too but am unable to do so.
Re: duplicate problem fix [message #419727 is a reply to message #419706] Tue, 25 August 2009 06:14 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Ok.
But you have to provide working test case.
And please specify clearly that which rows you want to delete and which rows you want to keep?

regards,
Delna
Re: duplicate problem fix [message #419732 is a reply to message #419727] Tue, 25 August 2009 06:42 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Can you show us what you tried in Oracle? I just hope you did not just copied query that worked for Mysql into Oracle database and expected it to run without any issue.

Hint. You need to add rowid in your query that works fine in MYSQL and then use that to delete from your table.
Re: duplicate problem fix [message #419733 is a reply to message #419727] Tue, 25 August 2009 06:43 Go to previous message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
Delete from EMRPATIENTPROBLEMS where rowid in
(Select rid from 
   (Select rowid rid,
            row_number() over
            (partition by 
                 problem_code, patient_id ORDER BY PROBLEM_ID DESC,rowid desc) rn
             from emrpatientproblems
    )
  where rn > 1 )
/




looks fine as i am not comfortable with oracle queries.anyway thanks.
Previous Topic: migration script needed
Next Topic: SQL Query performance
Goto Forum:
  


Current Time: Mon Dec 05 05:09:19 CST 2016

Total time taken to generate the page: 0.28183 seconds