Home » SQL & PL/SQL » SQL & PL/SQL » eliminate duplicate rows (merged same question by two different users)
eliminate duplicate rows (merged same question by two different users) [message #298845] Fri, 08 February 2008 00:22 Go to next message
aslammd
Messages: 3
Registered: February 2008
Junior Member
desc renter
Name Null? Type
---------------- -------- --------------
ID VARCHAR2(12)
NAME VARCHAR2(20)
ADDR VARCHAR2(40)
TELMOBNO NUMBER(12)

desc rent
Name Null? Type
---------------- -------- --------------
ID VARCHAR2(12)
RENTASON DATE
RENTAMT NUMBER(7,2)
HOLDINGNO NUMBER(5)
WARDNO NUMBER(3)

SELECT RENTER.ID,NAME,RENTASON,RENTAMT FROM RENTER,RENT WHERE RENTER.ID=RENT.ID;

ID NAME RENTASON RENTAMT
------------ -------------------- --------- ----------
RENT1 MD. ASIF 01-SEP-07 1800
RENT1 MD. ASIF 01-MAR-05 1600
RENT1 MD. ASIF 01-DEC-07 2000
RENT2 VICKKY 01-MAR-06 300

My problem is that I want to retreive only unique rows from rent table and which has condition that row retreived from rent table is max(rentason) condition also. Please somebody help me.
Re: How to eliminate duplicate rows [message #298848 is a reply to message #298845] Fri, 08 February 2008 00:25 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
It's useful to you:-

http://www.orafaq.com/faq/how_does_one_eliminate_duplicates_rows_from_a_table


regard,
Re: How to eliminate duplicate rows [message #298849 is a reply to message #298848] Fri, 08 February 2008 00:27 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm not sure he'd like to REMOVE them from a database; reading his post, I'd rather say that he'd like to eliminate duplicates from the result of a query.

If so, SELECT DISTINCT might help.

[EDIT] LOL, obviously NOT! I'd better stop answering questions when I'm not awake (there aren't any duplicate records in the result set!).

OK; it seems that you'll have to include the SELECT MAX(date_column) into the WHERE clause; something like this:
...              
FROM rent r, ...
WHERE r.rentamt = (SELECT MAX(r1.rentamt)
                   FROM rent r1
                   WHERE r1.id = r.id)

[Updated on: Fri, 08 February 2008 00:35]

Report message to a moderator

Re: How to eliminate duplicate rows [message #298854 is a reply to message #298845] Fri, 08 February 2008 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Regards
Michel
Re: How to eliminate duplicate rows [message #298856 is a reply to message #298848] Fri, 08 February 2008 00:50 Go to previous messageGo to next message
aslammd
Messages: 3
Registered: February 2008
Junior Member
I dont want to delete any rows from RENT table. Is there any method to select unique rows without deleteing duplicate rows ?
Pls Rply.
Re: How to eliminate duplicate rows [message #298859 is a reply to message #298856] Fri, 08 February 2008 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
See LittleFoot's answer.

Regards
Michel
Re: How to eliminate duplicate rows [message #298864 is a reply to message #298845] Fri, 08 February 2008 01:13 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Quote:
I'm not sure he'd like to REMOVE them from a database; reading his post, I'd rather say that he'd like to eliminate duplicates from the result of a query.



you were right, littlefoot.

regards,

[Updated on: Fri, 08 February 2008 01:23]

Report message to a moderator

Re: How to eliminate duplicate rows [message #298980 is a reply to message #298849] Fri, 08 February 2008 07:10 Go to previous messageGo to next message
aslammd
Messages: 3
Registered: February 2008
Junior Member
Pls send full line of code.

[Updated on: Fri, 08 February 2008 07:11]

Report message to a moderator

Re: How to eliminate duplicate rows [message #298982 is a reply to message #298845] Fri, 08 February 2008 07:16 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Quote:
Pls send full line of code.


Please take it as a homework for today Smile for implementing Littlefoot's Post.


Quote:
FROM rent r, ...
WHERE r.rentamt = (SELECT MAX(r1.rentamt)
FROM rent r1
WHERE r1.id = r.id)



Thumbs Up
Rajuvan.

[Updated on: Fri, 08 February 2008 07:21]

Report message to a moderator

How to Retrieve Conditional Query Rows [message #298988 is a reply to message #298845] Fri, 08 February 2008 07:28 Go to previous messageGo to next message
aslamsadia
Messages: 19
Registered: December 2006
Junior Member
DESCRIBE RENTER
Name Null? Type
---------------- -------- --------------
ID VARCHAR2(12)
NAME VARCHAR2(20)
ADDR VARCHAR2(40)
TELMOBNO NUMBER(12)

DESCRIBE RENT
Name Null? Type
---------------- -------- --------------
ID VARCHAR2(12)
RENTASON DATE
RENTAMT NUMBER(7,2)
HOLDINGNO NUMBER(5)
WARDNO NUMBER(3)

SELECT RENTER.ID,NAME,RENTASON,RENTAMT FROM RENTER,RENT WHERE RENTER.ID=RENT.ID;

ID NAME RENTASON RENTAMT
------------ -------------------- --------- ----------
RENT1 MD. ASIF 01-SEP-07 1800
RENT1 MD. ASIF 01-MAR-05 1600
RENT1 MD. ASIF 01-DEC-07 2000
RENT2 VICKKY 01-MAR-06 300

I want to retreive only rows which has condition that row retreived from rent table is MAX(RENTASON) condition. Rows with RENTAMT-1600 and RENTAMT-1800 not to be display in reult. Pls somebody help me.

[Updated on: Fri, 08 February 2008 07:29]

Report message to a moderator

Re: How to Retrieve Conditional Query Rows [message #298992 is a reply to message #298988] Fri, 08 February 2008 07:44 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

NO more Crossposting please

rajavu1 wrote on Fri, 08 February 2008 18:46
Quote:
Pls send full line of code.


Please take it as a homework for today Smile for implementing Littlefoot's Post.


Quote:
FROM rent r, ...
WHERE r.rentamt = (SELECT MAX(r1.rentamt)
FROM rent r1
WHERE r1.id = r.id)



Thumbs Up
Rajuvan.

[Updated on: Fri, 08 February 2008 07:45]

Report message to a moderator

Re: How to Retrieve Conditional Query Rows [message #298993 is a reply to message #298988] Fri, 08 February 2008 07:44 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
What is your query?
Have you tried anything yet?

By
Vamsi
Re: How to Retrieve Conditional Query Rows [message #298997 is a reply to message #298993] Fri, 08 February 2008 08:06 Go to previous messageGo to next message
aslamsadia
Messages: 19
Registered: December 2006
Junior Member
I have tried the below given query :-
SELECT RENTER.ID,NAME,ADDR,RENTASON,RENTAMT FROM RENTER,RENT WHERE RENTER.ID = (SELECT ID FROM RENT WHERE ID=RENTER.ID AND RENTASON = (SELECT MAX(RENTASON) FROM RENT WHERE ID=RENTER.ID));

the result set is :-
ID NAME ADDR RENTASON RENTAMT
------- -------------------- -------------------------------- --------- ----------
RENT1 ASIF Ranchi 01-MAR-05 1600
RENT2 VICKKY Dhanbad 01-MAR-05 1600
RENT1 ASIF Ranchi 01-MAR-06 300
*RENT2 VICKKY Dhanbad 01-MAR-06 300
RENT1 ASIF Ranchi 01-SEP-06 1800
RENT2 VICKKY Dhanbad 01-SEP-06 1800
*RENT1 ASIF Ranchi 01-DEC-07 2000
RENT2 VICKKY Dhanbad 01-DEC-07 2000
8 rows selected.

I want to retrieve only rows with "*" . Pls help. Sad
Re: How to Retrieve Conditional Query Rows [message #298999 is a reply to message #298997] Fri, 08 February 2008 08:34 Go to previous message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 08 February 2008 07:45
In addition, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Regards
Michel

First follow the rule, then get help.

Regards
Michel

Previous Topic: Oracle Import/Export
Next Topic: cursor
Goto Forum:
  


Current Time: Fri Dec 02 12:15:26 CST 2016

Total time taken to generate the page: 0.08441 seconds