Home » SQL & PL/SQL » SQL & PL/SQL » 2 rows contain same values but different columns (10g, R2, Windows XP Pro)
|
|
Re: 2 rows contain same values but different columns [message #358507 is a reply to message #358496] |
Tue, 11 November 2008 11:41   |
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 #358530 is a reply to message #358496] |
Tue, 11 November 2008 16:47   |
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   |
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 #358576 is a reply to message #358548] |
Tue, 11 November 2008 21:46   |
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 #358584 is a reply to message #358576] |
Tue, 11 November 2008 22:12   |
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 #358595 is a reply to message #358496] |
Tue, 11 November 2008 22:28   |
flyboy
Messages: 1903 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 condition into WHERE clause.
|
|
|
|
Re: 2 rows contain same values but different columns [message #358645 is a reply to message #358599] |
Wed, 12 November 2008 01:01   |
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.
|
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Feb 11 16:54:35 CST 2025
|