Home » SQL & PL/SQL » SQL & PL/SQL » 2 rows contain same values but different columns (10g, R2, Windows XP Pro)
icon6.gif  2 rows contain same values but different columns [message #358496] Tue, 11 November 2008 10:46 Go to next message
leonardh
Messages: 10
Registered: November 2008
Junior Member
Dear All,

I need your help with the SQL.

I have the following table:

( Table_1 --> see the Table.txt )

Desirable result set will be:

( Table_2 --> see the Table.txt )

As you can see, the problem I am having now is:
How to tell Oracle that the table has duplicated values,
although in different columns?

I have done some thinking but I cannot proceed
anymore:

CASE WHEN col_1 = col_2 and col_2 = col_1 THEN ... ?


Your help will be truly appreciated.


Sincerely,
Leo

  • Attachment: Table.txt
    (Size: 0.43KB, Downloaded 128 times)
Re: 2 rows contain same values but different columns [message #358506 is a reply to message #358496] Tue, 11 November 2008 11:32 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
For two columns compared, I would use LEAST and GREATEST functions, then remove the duplicates using DISTINCT.

By the way, the design looks strange to me whatever it serves for.
Re: 2 rows contain same values but different columns [message #358507 is a reply to message #358496] Tue, 11 November 2008 11:41 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@leonardh,
Table_1 (existing table)

=====================
Col_1 | Col_2 | Col_3
------|-------|------
  A   |   B   |   1
  B   |   A   |   1
  A   |   C   |   2
  C   |   A   |   2
  D   |   E   |   3
  E   |   D   |   3
=====================

Table_2 (desired result)

=====================
Col_1 | Col_2 | Col_3
------|-------|------
  A   |   B   |   1
  A   |   C   |   2
  D   |   E   |   3
=====================


Can you please explain on what basis you are telling there are duplicate records? I didn't quite understand the problem. For instance take the following two records:
=====================
Col_1 | Col_2 | Col_3
------|-------|------
  A   |   B   |   1
  B   |   A   |   1

So what made you select the following out of two?
Table_2 (desired result)

=====================
Col_1 | Col_2 | Col_3
------|-------|------
  A   |   B   |   1

One thing you have to understand is the record set (A, B, 1) is not equal to the record set (B, A, 1). i.e. you can't consider the two sets to be duplicates.

Regards,
Jo
Re: 2 rows contain same values but different columns [message #358513 is a reply to message #358496] Tue, 11 November 2008 12:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Many of us can't or don't want to download files so post tables description or better create table and insert statements as lons as with the result you want.
For this result you have to explain why it is this one.

Also 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 (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
And always post your Oracle version (4 decimals).

Regards
Michel
Re: 2 rows contain same values but different columns [message #358528 is a reply to message #358496] Tue, 11 November 2008 16:33 Go to previous messageGo to next message
lakshmis
Messages: 102
Registered: November 2008
Location: India
Senior Member

SELECT * FROM TABLE1 WHERE ROWID IN
SELECT MIN(ROWID) FROM TABLE1 GROUP BY COL3)
ORDER BY COL3;

I hope this helps you.
Regards,
Lakshmi
Re: 2 rows contain same values but different columns [message #358530 is a reply to message #358496] Tue, 11 November 2008 16:47 Go to previous messageGo to next message
leonardh
Messages: 10
Registered: November 2008
Junior Member
Dear All,

I am surprised to see 3 responses within 3 hours.
I did not expect a response would be so soon.
Thank you beforehand.


To FLYBOY,

Thanks for the functions you mentioned.

The table I was referring to is a result of a query.
The table is not designed like that.


To JOICEJOHN,

The Table_2 can be either A B 1 or B A 1.
Here is what I mean:
In Table_1 there are 2 rows with almost identical values,
namely A B 1 (row 1) and B A 1 (row 2).
In my humble opinion, I thought I could compare them and only
get 1 result; either A B 1 or B A 1.
I will explain them below for greater clarity.

Since you tell me so, now I will consider that those 2 rows
are not equal. Thanks for that.


To Michel Cadot,

My version is this one Oracle Database 10g Enterprise Edition
Release 10.2.0.1.0.

The reason I used .txt was because when I previewed the tables
they were a mess. I didn't know that I could use the tools that
you just mention. Thanks for that.

--000--

Now, here is what I intend to do.
I have a table consists of NAME, BIRTH_DATE and ID.
All I want to do is to find out whose birthday matched whose,
based on only the day and month (not the year).
For example, in the original table I have the following
records:

NAME BIRTH_DATE ID
Tony Blair 17-FEB-58 1
Prince Charles 23-APR-75 2
James Bond 30-NOV-80 3
Paul Merton 06-FEB-63 4
Paris Hilton 30-NOV-82 5
Albert Einstein 18-JUL-63 6
Simon Cowell 22-FEB-72 7
Thomas Edison 03-MAR-52 8
John Lennon 30-NOV-70 9
Massimo Ranieri 11-MAY-75 10
Barack Obama 03-MAR-70 11
Jean Lucas 17-FEB-88 12
Van Disel 23-APR-90 13

As you can see, Tony Blair's birthday (day and month) matches
Jean Lucas (17-FEB). Prince Charles' matches Van Disel (23-APR).
James Bond's matches Paris Hilton's and John Lennon's.

By using the following SQL, I got the Table_1 (as I mentioned earlier).

SELECT a1.NAME AS Name1,
a2.NAME AS Name2,
(a1.Id * a2.Id) + (a1.Id + a2.Id) AS The_Id
FROM Actor a1
INNER JOIN Actor a2
ON To_char(a1.Birth_Date,'DD-MON') = To_char(a2.Birth_Date,'DD-MON')
WHERE a1.Id <> a2.Id
ORDER BY The_ID


However, Table_1 is a messy one, because I got

NAME1 NAME2 THE_ID
Tony Blair Jean Lucas 25
Jean Lucas Tony Blair 25

Thus, basically what I want is pretty simple:

NAME1 NAME2
Tony Blair Jean Lucas
Prince Charles Van Disel
James Bond John Lennon
James Bond Paris Hilton
Paris Hilton John Lennon

And, as you may aware, I really don't care about the names in the column NAME1 and NAME2.
They can be swapped as long as they contain the information like Tony Blair's birthday
is the same as Jean Lucas' (or you can say Jean Lucas' birthday is just like Tony Blair's).

The use of The_ID column is not necessary.
If you can think of getting rid of that column, be my guest.


Once again, hopefully this will clarify a bit.


Regards,
Leo
Re: 2 rows contain same values but different columns [message #358531 is a reply to message #358528] Tue, 11 November 2008 17:05 Go to previous messageGo to next message
leonardh
Messages: 10
Registered: November 2008
Junior Member
Hi Lakshmi,


Thanks for your response.

I tried your SQL with some modification:

SELECT   MIN(i.The_Id),
         i.Name1,
         i.Name2
FROM     (SELECT   a1.NAME AS Name1,
                   a2.NAME AS Name2,
                   (a1.Id * a2.Id) + (a1.Id + a2.Id) AS The_Id
          FROM     Actor a1
                   INNER JOIN Actor a2
                     ON To_char(a1.Birth_Date,'DD-MON') = To_char(a2.Birth_Date,'DD-MON')
          WHERE    a1.Id <> a2.Id
          ORDER BY The_Id) i
GROUP BY i.Name1,
         i.Name2
ORDER BY i.Name1
;


However, the result is still like Table_1.
The names in either columns keep repeated.
And I want only unique names.
Please refer to my response to the other 3 users for greater clarity.

Much appreciated your effort.


Regards,
Leo

[Updated on: Tue, 11 November 2008 17:30]

Report message to a moderator

Re: 2 rows contain same values but different columns [message #358548 is a reply to message #358496] Tue, 11 November 2008 19:21 Go to previous messageGo to next message
modhiyakomal
Messages: 49
Registered: November 2008
Location: US
Member
Run the following Query ,it works i have tried myself. Hope it works for you.

delete from table1
where rowid<>(select max(rowid)
from table1 b where b.col_3=table1.col_3 and b.col_3=table1.col_3);


Kindest Regards,
Komal

Re: 2 rows contain same values but different columns [message #358576 is a reply to message #358548] Tue, 11 November 2008 21:46 Go to previous messageGo to next message
leonardh
Messages: 10
Registered: November 2008
Junior Member
Hi Komal,

I try your query and it works.

So, let me summarise everything here:

1. Create a new table called actor
DROP TABLE actor
;

CREATE TABLE actor
( name        varchar2(50)
, birth_date  date
, id          number(2)
)
;

INSERT INTO actor VALUES ('Tony Blair', '17-FEB-58', 1);
INSERT INTO actor VALUES ('Prince Charles', '23-Apr-75', 2);
INSERT INTO actor VALUES ('James Bond', '30-Nov-80', 3);
INSERT INTO actor VALUES ('Paul Merton', '06-Feb-63', 4);
INSERT INTO actor VALUES ('Paris Hilton', '30-Nov-82', 5);
INSERT INTO actor VALUES ('Albert Einstein', '18-Jul-63', 6);
INSERT INTO actor VALUES ('Simon Cowell', '22-Feb-72', 7);
INSERT INTO actor VALUES ('Thomas Edison', '03-Mar-52', 8);
INSERT INTO actor VALUES ('John Lennon', '30-Nov-70', 9);
INSERT INTO actor VALUES ('Massimo Ranieri', '11-May-75', 10);
INSERT INTO actor VALUES ('Barack Obama', '03-Mar-70', 11);
INSERT INTO actor VALUES ('Jean Lucas', '17-Feb-88', 12);
INSERT INTO actor VALUES ('Van Disel', '23-Apr-90', 13);


SELECT * 
FROM actor
;
--------------------------------
NAME             BIRTH_DATE  ID  
Tony Blair       17-FEB-58   1  
Prince Charles   23-APR-75   2  
James Bond       30-NOV-80   3  
Paul Merton      06-FEB-63   4  
Paris Hilton     30-NOV-82   5  
Albert Einstein  18-JUL-63   6  
Simon Cowell     22-FEB-72   7  
Thomas Edison    03-MAR-52   8  
John Lennon      30-NOV-70   9  
Massimo Ranieri  11-MAY-75   10  
Barack Obama     03-MAR-70   11  
Jean Lucas       17-FEB-88   12  
Van Disel        23-APR-90   13  


2. Write a query to find out same birthday (day and month only):
DROP TABLE temp_table_1
;

CREATE TABLE temp_table_1
AS                             
SELECT     a1.NAME AS name1,
           a2.NAME AS name2,
           (a1.id*a2.id)+(a1.id+a2.id) AS the_id
FROM       actor a1
INNER JOIN actor a2
ON         to_char(a1.birth_date,'DD-MON') = to_char(a2.birth_date,'DD-MON')
WHERE      a1.id <> a2.id
ORDER BY   the_id
;


SELECT *
FROM temp_table_1
;
----------------------------------------
NAME1            NAME2            THE_ID  
James Bond       Paris Hilton     23  
Paris Hilton     James Bond       23  
Tony Blair       Jean Lucas       25  
Jean Lucas       Tony Blair       25  
James Bond       John Lennon      39  
John Lennon      James Bond       39  
Van Disel        Prince Charles   41  
Prince Charles   Van Disel        41  
John Lennon      Paris Hilton     59  
Paris Hilton     John Lennon      59  
Thomas Edison    Barack Obama     107  
Barack Obama     Thomas Edison    107  


3. Tidy up the result of the above query:
DELETE FROM temp_Table_1
WHERE       RowId <> (SELECT MAX(RowId)
                      FROM   temp_Table_1 b
                      WHERE  b.The_Id = temp_Table_1.The_Id);


SELECT name1, name2
FROM temp_table_1
;
------------------------------
NAME1           NAME2  
Paris Hilton    James Bond  
Jean Lucas      Tony Blair  
John Lennon     James Bond  
Prince Charles  Van Disel  
Paris Hilton    John Lennon  
Barack Obama    Thomas Edison  


Thank you very much, Komal!

Just one more question, is there any way at all to get the result
like in the last table (temp_table_1 after the tidy up) without
any assistance or involvement of ID.

I mean can we make a query - without ID at all - to get that table?


Best regards,
Leo

Re: 2 rows contain same values but different columns [message #358583 is a reply to message #358496] Tue, 11 November 2008 22:12 Go to previous messageGo to next message
leonardh
Messages: 10
Registered: November 2008
Junior Member
Hi Komal,


I tried every way to make a query without the help of ID.
It wouldn't work.

So, this problem as far as I am concerned is closed.

Once again, thanks for your help.


Best regards,
Leo
Smile
Re: 2 rows contain same values but different columns [message #358584 is a reply to message #358576] Tue, 11 November 2008 22:12 Go to previous messageGo to next message
modhiyakomal
Messages: 49
Registered: November 2008
Location: US
Member
i think we can do it without using rowid, bcz here we r using rowid to identify rows uniquely. so if your table contains any column which one works as a primary key or unique key then u can do it. let me try it on your tables, i'll let u know once will get answer. so your prob is solved?
Re: 2 rows contain same values but different columns [message #358586 is a reply to message #358584] Tue, 11 November 2008 22:13 Go to previous messageGo to next message
leonardh
Messages: 10
Registered: November 2008
Junior Member
Yes, Komal.
Problem is solved.

Smile
Re: 2 rows contain same values but different columns [message #358587 is a reply to message #358586] Tue, 11 November 2008 22:18 Go to previous messageGo to next message
modhiyakomal
Messages: 49
Registered: November 2008
Location: US
Member
great
Cool
Re: 2 rows contain same values but different columns [message #358588 is a reply to message #358586] Tue, 11 November 2008 22:20 Go to previous messageGo to next message
modhiyakomal
Messages: 49
Registered: November 2008
Location: US
Member
so without using rowid u got it? if yes then please let me know.
thanks
Re: 2 rows contain same values but different columns [message #358589 is a reply to message #358496] Tue, 11 November 2008 22:23 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

yes , flyboy has already suggested the Way ..

Quote:
For two columns compared, I would use LEAST and GREATEST functions, then remove the duplicates using DISTINCT.


Using LEAST and GREATEST . Try in that way .

Smile
Rajuvan.

[Updated on: Tue, 11 November 2008 22:23]

Report message to a moderator

Re: 2 rows contain same values but different columns [message #358590 is a reply to message #358588] Tue, 11 November 2008 22:24 Go to previous messageGo to next message
leonardh
Messages: 10
Registered: November 2008
Junior Member
Hi Komal,

No, I didn't solve it without the help of ID.
I tried any way that I could think of.
So, I think the query must use ID.

Best regards,
Leo


Re: 2 rows contain same values but different columns [message #358591 is a reply to message #358590] Tue, 11 November 2008 22:26 Go to previous messageGo to next message
modhiyakomal
Messages: 49
Registered: November 2008
Location: US
Member
yes query must use id bcz rowid will not be appear in your o/p so if u use it in your query it dsnt matter. if it solve your prob.
right? Smile
Re: 2 rows contain same values but different columns [message #358592 is a reply to message #358589] Tue, 11 November 2008 22:27 Go to previous messageGo to next message
leonardh
Messages: 10
Registered: November 2008
Junior Member
Hi Rajuvan,


Thanks for the response.

The problem is now solved, thanks to Komal.

Honestly I haven't tried the FLYBOY's suggestion.
Perhaps next time.


Regards,
Leo
Re: 2 rows contain same values but different columns [message #358594 is a reply to message #358591] Tue, 11 November 2008 22:28 Go to previous messageGo to next message
leonardh
Messages: 10
Registered: November 2008
Junior Member
Yes, Komal.
Absolutely correct.
Re: 2 rows contain same values but different columns [message #358595 is a reply to message #358496] Tue, 11 November 2008 22:28 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
The table I was referring to is a result of a query.
The table is not designed like that.
You may nest queries (make queries based on result of another queries), so I do not see here any problem. Especially as in following posts you store that result in a table and do not mind deleting from it.

Now I am curious whether this original query is supposed to return "duplicates". You may rid of them in the beginning simply adding
a1.name < a2.name
condition into WHERE clause.
Re: 2 rows contain same values but different columns [message #358599 is a reply to message #358496] Tue, 11 November 2008 22:40 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

But Deleting using the ID is not a good Method always , though it will cater your need temporarily.

Suppose what will happen if the records are like this with 4 ID of 25 .

SQL> SELECT *
  2  FROM temp_table_1;

NAME1                          NAME2                              THE_ID
------------------------------ ------------------------------ ----------
James Bond                     Paris Hilton                           23
Paris Hilton                   James Bond                             23
Tony Blair                     Jean Lucas                             25
Jean Lucas                     Tony Blair                             25
James Bond                     John Lennon                            25
John Lennon                    James Bond                             25
Van Disel                      Prince Charles                         41
Prince Charles                 Van Disel                              41
John Lennon                    Paris Hilton                           59
Paris Hilton                   John Lennon                            59
Thomas Edison                  Barack Obama                          107
Barack Obama                   Thomas Edison                         107

12 rows selected.

SQL>


Now see, what will happen ...

SQL> DELETE FROM temp_Table_1
  2  WHERE       RowId <> (SELECT MAX(RowId)
  3                        FROM   temp_Table_1 b
  4                        WHERE  b.The_Id = temp_Table_1.The_Id);

7 rows deleted.

SQL> SELECT *
  2  FROM temp_table_1;

NAME1                          NAME2                              THE_ID
------------------------------ ------------------------------ ----------
Paris Hilton                   James Bond                             23
John Lennon                    James Bond                             25
Prince Charles                 Van Disel                              41
Paris Hilton                   John Lennon                            59
Barack Obama                   Thomas Edison                         107

SQL>


(Jean Lucas , Tony Blair) combination is removed !!!!

As per the information available , it is possible to have the Same ID for different combination .In that case Deltetion based on ID is NOT at all a good method.

Smile
Rajuvan.


Re: 2 rows contain same values but different columns [message #358645 is a reply to message #358599] Wed, 12 November 2008 01:01 Go to previous messageGo to next message
modhiyakomal
Messages: 49
Registered: November 2008
Location: US
Member
here in where clause you are specifying only one condition, but in name2 column there r duplicate values, so solution is that u can add AND condition in where clause.....
so its not necessary that rowid is not always a best way, but it works and u get proper result. if u want to use it properly.... so in your case u can use AND condition.... prob will be solved.
Re: 2 rows contain same values but different columns [message #358660 is a reply to message #358599] Wed, 12 November 2008 01:45 Go to previous messageGo to next message
modhiyakomal
Messages: 49
Registered: November 2008
Location: US
Member
SQL> select * from temp_table_1;

NAME1 NAME2 THE_ID
------------------------------ ------------------------------ ----------
James Bond Paris Hilton 23
Paris Hilton James Bond 23
Tony Blair Jean Lucas 25
Jean Lucas Tony Blair 25
James Bond John Lennon 25
John Lennon James Bond 25
Van Disel Prince Charles 41
Prince Charles Van Disel 41
John Lennon Paris Hilton 59
Paris Hilton John Lennon 59
Thomas Edison Barack Obama 107

NAME1 NAME2 THE_ID
------------------------------ ------------------------------ ----------
Barack Obama Thomas Edison 107

12 rows selected.

SQL> delete from temp_table_1
2 where rowid<>(select MAX(rowid)
3 from temp_table_1 b
4 where b.name1=temp_table_1.name1 OR b.name2=temp_table_1.name2 OR b.the_id=t
emp_table_1.the_id);

9 rows deleted.

SQL> select * from temp_table_1;

NAME1 NAME2 THE_ID
------------------------------ ------------------------------ ----------
Prince Charles Van Disel 41
Paris Hilton John Lennon 59
Barack Obama Thomas Edison 107

this is the o/p what u want............Smile
Re: 2 rows contain same values but different columns [message #358664 is a reply to message #358496] Wed, 12 November 2008 01:56 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

NO .. ( as far as I understood)

Why did you ignore the following combination ?

James Bond Paris Hilton
Tony Blair Jean Lucas and
James Bond John Lennon

Smile
Rajuvan.
Re: 2 rows contain same values but different columns [message #358671 is a reply to message #358664] Wed, 12 November 2008 02:15 Go to previous messageGo to next message
modhiyakomal
Messages: 49
Registered: November 2008
Location: US
Member
ok so which output u expect?
Re: 2 rows contain same values but different columns [message #358675 is a reply to message #358671] Wed, 12 November 2008 02:22 Go to previous message
modhiyakomal
Messages: 49
Registered: November 2008
Location: US
Member
ok i got your auestion, will try.
Previous Topic: Exception PLSQL
Next Topic: Procedure
Goto Forum:
  


Current Time: Wed Dec 07 04:50:20 CST 2016

Total time taken to generate the page: 0.09265 seconds