I have an oracle query that is typically giving me three rows of results. Two are identical while the third is simply an extra row. How can I modify this query to obtain only the unique results. Here is the query statement:
select prt.oid, it.itemname, it1.itemname, prt1.oid from jrtp_rdb.jrtepartoccurrence prt
join jrtp_rdb.jnameditem it on it.oid = prt.oid
join jrtp_rdb.xdistribports x on x.oidorigin = prt.oid
join jrtp_rdb.xflowports x1 on x1.oiddestination = x.oiddestination
join jrtp_rdb.xgeneratesconnectionitems x2 on x2.oidorigin= x1.oidorigin
join jrtp_rdb.xownsimplieditems x3 on x3.oiddestination = x2.oiddestination
join jrtp_rdb.jnameditem it1 on it1.oid = x3.oidorigin
join jrtp_rdb.jrtepartoccurrence prt1 on prt1.oid = it1.oid
where prt.oid in
('00013885000000004C00426DEC534269');
The results are shown in the table below:
00013885000000004C00426DEC534269 Flange-120491 Gate Valve-65650 0001388500000000AB3BEAC87354D9BE
00013885000000004C00426DEC534269 Flange-120491 Gate Valve-65650 0001388500000000AB3BEAC87354D9BE
00013885000000004C00426DEC534269 Flange-120491 Flange-120491 00013885000000004C00426DEC534269
In this case I just want the first row since it gives the name of the flange connecting to the gate valve. How can I modify the query to just obtain the unique row and get rid of the extra third row?
How about this:
select distinct prt.oid, it.itemname, it1.itemname, prt1.oid from jrtp_rdb.jrtepartoccurrence prt
join jrtp_rdb.jnameditem it on it.oid = prt.oid
join jrtp_rdb.xdistribports x on x.oidorigin = prt.oid
join jrtp_rdb.xflowports x1 on x1.oiddestination = x.oiddestination
join jrtp_rdb.xgeneratesconnectionitems x2 on x2.oidorigin= x1.oidorigin
join jrtp_rdb.xownsimplieditems x3 on x3.oiddestination = x2.oiddestination
join jrtp_rdb.jnameditem it1 on it1.oid = x3.oidorigin
join jrtp_rdb.jrtepartoccurrence prt1 on prt1.oid = it1.oid
where prt.oid in
('00013885000000004C00426DEC534269') and prt.oid<>prt1.oid