How to select only duplicate values, but with one different column?

Саят Оразов 0
Viewed 60

I have a table, which looks like this:

Aircraft WorkOrder EngOrder Description PartNo InstPosition
KHB 21 45 engine 1 pw123 E1
KHB 21 45 engine 4 pw123 E2
KHB 22 45 engine 2 pw122 E1
KBG 31 55 rotor engine v123 E2
KBG 36 51 engine 9 v156 E1
KBG 31 55 engine comp v123 E1

I need to select only rows which are similar in WorkOrder, EngOrder and PartNO, but different in InstPosition. My resulting table should look like this:

Aircraft WorkOrder EngOrder Description PartNo InstPosition
KHB 21 45 engine 1 pw123 E1
KHB 21 45 engine 4 pw123 E2
KBG 31 55 rotor engine v123 E2
KBG 31 55 engine comp v123 E1

I tried to use self join, but it returns empty table:

SELECT a.* 
FROM table a
INNER JOIN b ON a.WorkOrder = b.WorkOrder 
       AND a.EngOrder = b.EngOrder 
       AND a.PartNO = b.PartNO
       AND a.InstPosition != b.InstPosition ```
oracle-sqldeveloper oracle sql
4 Answers

You don't need to join.

This:

I need to select only rows which are similar in WorkOrder, EngOrder and PartNO, but different in InstPosition.

Sound's like count distinct InstPosition in window for me.

select 
Aircraft,
WorkOrder,
EngOrder,
Description,
PartNo,
InstPosition
from
(
  select 
  Aircraft,
  WorkOrder,
  EngOrder,
  Description,
  PartNo,
  InstPosition,
  count(distinct InstPosition) over(partition by WorkOrder, EngOrder , PartNO) as dist_cnt
  from  a
)
where dist_cnt > 1
;
RobertG0

We can use an aggregation approach here

WITH cte AS (
    SELECT WorkOrder, EngOrder, PartNO
    FROM yourTable
    GROUP BY WorkOrder, EngOrder, PartNO
    HAVING MIN(InstPosition) <> MAX(InstPosition)
)

SELECT t1.*
FROM yourTable t1
INNER JOIN cte t2
    ON t2.WorkOrder = t1.WorkOrder AND
       t2.EngOrder  = t1.EngOrder  AND
       t2.PartNO    = t1.PartNO;

The CTE finds all tuples with WorkOrder, EngOrder, and PartNO having at least 2 different InstPosition values. The outer join then filters the original table.

Tim Biegeleisen0

I need to select only rows which are similar in WorkOrder, EngOrder and PartNO, but different in InstPosition.

You can do that basically just the way you expressed it in English. Find any row where there is at least one other row having the same WorkOrder, EngOrder, and PartNo but different InstPosition.

Here's how to translate that to SQL:

SELECT t.*
FROM   your_table t
WHERE  EXISTS ( SELECT 'similar record'
                FROM   your_table t2
                WHERE  t2.WorkOrder = t.WorkOrder
                AND    t2.EngOrder = t.EngOrder
                AND    t2.PartNo = t.PartNo
                -- simple logic below assumes InsPosition is NOT NULL
                AND    t2.InstPosition <> t.InstPosition 
              )
Matthew McPeak0

You could use below solution for that purpose :

SELECT Aircraft, WorkOrder, EngOrder, Description, PartNo, InstPosition
FROM (
  SELECT t1.*, COUNT(DISTINCT InstPosition)OVER(PARTITION BY WorkOrder, EngOrder, PartNo)cnt
  FROM Your_Table t1
) t2
WHERE cnt > 1
ORDER BY WorkOrder, EngOrder, PartNo
;

demo

Mahamoutou0
Related