OraFAQ Forum: SQL & PL/SQL » How can I get rid of the same records and reserve only one of them in a table.

Home » SQL & PL/SQL » SQL & PL/SQL » How can I get rid of the same records and reserve only one of them in a table.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How can I get rid of the same records and reserve only one of them in a table. [message #1198]

Wed, 10 April 2002 18:36

mhy
Messages: 15
Registered: March 2001

Junior Member

dear sir,

How can I get rid of the same records and reserve only one of them in a table.

fg,I have a table namend reconsume ,there is about 200000 rows in it,some of the record are same,
So I want to get rid the rest of them .

the records before update are:

LOGNAME ACCTSTART ACCTSTOPT FEE
-------------------------------- --------- --------- ----------
2821001032 17-MAR-02 17-MAR-02 .32
2821001032 17-MAR-02 17-MAR-02 .32
2821001045 14-MAR-02 14-MAR-02 4.28
2821001045 14-MAR-02 14-MAR-02 4.28
2821001046 09-MAR-02 10-MAR-02 7.21
2821001046 09-MAR-02 10-MAR-02 7.21
2821001046 09-MAR-02 10-MAR-02 7.21
2821001046 09-MAR-02 10-MAR-02 7.21
2821001069 09-MAR-02 09-MAR-02 .34
2821001069 09-MAR-02 09-MAR-02 .34
2821001149 18-MAR-02 18-MAR-02 2.72
2821001149 18-MAR-02 18-MAR-02 2.72
2821001150 11-MAR-02 11-MAR-02 .12
2821001150 11-MAR-02 11-MAR-02 .12

and I want to get get result as this:

LOGNAME ACCTSTART ACCTSTOPT FEE
-------------------------------- --------- --------- ----------
2821001032 17-MAR-02 17-MAR-02 .32
2821001045 14-MAR-02 14-MAR-02 4.28
2821001046 09-MAR-02 10-MAR-02 7.21
2821001069 09-MAR-02 09-MAR-02 .34
2821001149 18-MAR-02 18-MAR-02 2.72
2821001150 11-MAR-02 11-MAR-02 .12

how can I do?

thanks a lot!

Report message to a moderator

Re: How can I get rid of the same records and reserve only one of them in a table. [message #1199 is a reply to message #1198]

Wed, 10 April 2002 19:34

R.SIVAKUMAR
Messages: 48
Registered: February 2002

Member

from FOLLOWING QUERY, U CAN AVOID DUPLICATE RECORDS

SELECT * FROM reconsume A
WHERE ROWID>=(SELECT MAX(ROWID) FROM
reconsume B WHERE A.LOGNAME =B.LOGNAME );

IF U WANT TO DELETE THE DUPLICATE RECORDS, U CAN
USE THE FOLLOWING QUERY

DELETE FROM reconsume A
WHERE ROWID>=(SELECT MAX(ROWID) FROM
reconsume B WHERE A.LOGNAME =B.LOGNAME );

I THINK , IT WILL HELP U

BYE
SIVA

Report message to a moderator

Re: How can I get rid of the same records and reserve only one of them in a table. [message #1224 is a reply to message #1198]

Fri, 12 April 2002 06:09

DUP_QUESTION
Messages: 1
Registered: April 2002

Junior Member

SIVA,
The reply for deleting the duplicates will remove all the records which are single records and also that are duplicates with rowid >= max(rowid). Can you be specific how to delete only the duplicate records and not all the max(rowid).

Thanks

Report message to a moderator

Previous Topic:	Import a file
Next Topic:	Insert in Pl/sql

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 08:55:22 CDT 2024