Duplicate Data [message #637738] |
Sat, 23 May 2015 02:03 |
|
plsql_guy
Messages: 6 Registered: May 2015
|
Junior Member |
|
|
Hi Guys,
Recently I was asked one question in an Interview and I was not able to figure it out. May be you guys can help me on that. Just curious to know the answer.
There is one table where the distance is stored between two cities. But there are two entries for each distance. One from CITY A to CITY B and then again from CITY B to CITY A which is a replica of what we store First. e.g one entry is DELHI to MUMBAI - DISTANCE 2000 and the other entry is MUMBAI to DELHI - DISTANCE 2000. I just need one unique entry not the other one. Can you please help me with the query which will only list out unique records.
CREATE TABLE distance
(
city1 VARCHAR2(20),
city2 VARCHAR2(20),
dist NUMBER
);
INSERT INTO distance
VALUES ('DELHI',
'MUMBAI',
4000);
INSERT INTO distance
VALUES ('MUMBAI',
'DELHI',
4000);
INSERT INTO distance
VALUES ('DELHI',
'PUNE',
2000);
INSERT INTO distance
VALUES ('PUNE',
'DELHI',
2000);
INSERT INTO distance
VALUES ('MUMBAI',
'PUNE',
6000);
INSERT INTO distance
VALUES ('PUNE',
'MUMBAI',
6000);
COMMIT;
I am looking for the below output :
CITY1 CITY2 DISTANCE
DELHI MUMBAI 4000
DELHI PUNE 2000
MUMBAI PUNE 6000
Thanks!!!
--moderator update: correcte the closing [code] tag
[Updated on: Sat, 23 May 2015 02:26] by Moderator Report message to a moderator
|
|
|
Re: Duplicate Data [message #637740 is a reply to message #637738] |
Sat, 23 May 2015 02:26 |
John Watson
Messages: 8929 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Welcome to the forum. This looks more like a college homework question than an interview question. You need to remove duplicate rows, defining a duplicate of city1||city2 as being city2||city1. I would write a query that SELECTs from DISTANCE with a sub-query in the predicate that excludes rows that match CITY1||CITY2 with CITY2||CITY1.
|
|
|
|
|
|
|
|
Re: Duplicate Data [message #637749 is a reply to message #637743] |
Sat, 23 May 2015 06:57 |
Solomon Yakobson
Messages: 3273 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Michel Cadot wrote on Sat, 23 May 2015 04:20
If you want all the rows but no duplicates you have to add a condition in your subquery to not exclude both duplicates that is ensure that one is kept.
Or better to get rid of subquery altogether:
select least(city1,city2) city1,
greatest(city1,city2) city2,
dist
from distance
group by least(city1,city2),
greatest(city1,city2),
dist
/
CITY1 CITY2 DIST
-------------------- -------------------- ----------
MUMBAI PUNE 6000
DELHI MUMBAI 4000
DELHI PUNE 2000
SQL>
SY.
|
|
|
|
|
|
|
Re: Duplicate Data [message #637755 is a reply to message #637754] |
Sat, 23 May 2015 09:00 |
Solomon Yakobson
Messages: 3273 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Unique FBI:
SQL> create unique index distance_uidx1
2 on distance(
3 least(city1,city2),
4 greatest(city1,city2)
5 )
6 /
Index created.
SQL> INSERT INTO distance
2 VALUES ('DELHI',
3 'MUMBAI',
4 4000);
1 row created.
SQL>
SQL> INSERT INTO distance
2 VALUES ('MUMBAI',
3 'DELHI',
4 4000);
INSERT INTO distance
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.DISTANCE_UIDX1) violated
SQL>
SY.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: Duplicate Data [message #637912 is a reply to message #637738] |
Fri, 29 May 2015 00:32 |
|
jgjeetu
Messages: 373 Registered: July 2013 Location: www.Orafaq.com/Forum
|
Senior Member |
|
|
and what about this:-
CITY1 CITY2 CITY3 DISTANCE
DELHI MUMBAI PUNE 4000
PUNE DELHI MUMBAI 4000
MUMBAI PUNE DELHI 4000
PUNJAB HARYANA UP 6000
UP HARYANA PUNJAB 6000
HARYANA PUNJAB UP 6000
REQUIRED OUTPUT:-
CITY1 CITY2 CITY3 DISTANCE
DELHI MUMBAI PUNE 4000
HARYANA PUNJAB UP 6000
[Edit MC: Change color to avoid confusion with original question.]
[Updated on: Fri, 29 May 2015 00:48] by Moderator Report message to a moderator
|
|
|
|
Re: Duplicate Data [message #637914 is a reply to message #637738] |
Fri, 29 May 2015 01:34 |
|
msol25
Messages: 396 Registered: June 2011
|
Senior Member |
|
|
Dear Michel,
As per question given by OP's,I hope we can give like this :
select *
from
(
select city1,
city2,
dist,
row_number() over (partition by dist order by city1,city2,dist) as rnk
from distance
)
where rnk = 1;
I hope in this case partition based on dist and ordering based on city1,city2,dist.In this ordering will be done based on city1,city2,dist.
Here,Should have restriction that ordering will be done based on city1,city2 column ASCII Value for displaying output.Like ASCII for "D" is 68 and "M" is having 77.So Accoringly output will get prioritize for assigning the rank and accordingly we have to display where rank = 1.
|
|
|
|
|
|
Re: Duplicate Data [message #638458 is a reply to message #637738] |
Fri, 12 June 2015 02:54 |
|
nciteamo
Messages: 27 Registered: October 2014
|
Junior Member |
|
|
select city2, city1 ,dist from(
select rownum id, a.* from distance a)
where id in (select id+(id+1)-1 from (select rownum id, a.* from distance a))
i just wanna try to help and to be more active in this forum, sorry if it goes wrong
[Updated on: Fri, 12 June 2015 02:56] Report message to a moderator
|
|
|
Re: Duplicate Data [message #638459 is a reply to message #638458] |
Fri, 12 June 2015 03:06 |
cookiemonster
Messages: 13920 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
1) this:
is a pointlessly complicated way of writing this:
2) The whole query could be rewritten as this:
select city2, city1 ,dist from(
select rownum id, a.* from distance a)
where mod(id,2) = 0;
3) You're assuming the duplicates will come together in the inline view query, it's highly doubtful that they will
|
|
|
Re: Duplicate Data [message #638461 is a reply to message #638458] |
Fri, 12 June 2015 03:11 |
|
Michel Cadot
Messages: 68641 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Did you try your query with the rows I added?
It does not work.
The correct result is the one returned by Solomon's query:
SQL> select * from distance;
CITY1 CITY2 DIST
-------------------- -------------------- ----------
DELHI MUMBAI 4000
MUMBAI DELHI 4000
DELHI PUNE 2000
PUNE DELHI 2000
MUMBAI PUNE 6000
PUNE MUMBAI 6000
PARIS MARSEILLE 800
PARIS ATHENS 2000
8 rows selected.
SQL> select least(city1,city2) city1,
2 greatest(city1,city2) city2,
3 dist
4 from distance
5 group by least(city1,city2),
6 greatest(city1,city2),
7 dist
8 /
CITY1 CITY2 DIST
-------------------- -------------------- ----------
MUMBAI PUNE 6000
MARSEILLE PARIS 800
ATHENS PARIS 2000
DELHI MUMBAI 4000
DELHI PUNE 2000
5 rows selected.
|
|
|