I have a table, which looks like this:

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:

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 ```

CodePudding user response：

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.

CodePudding user response：

select  Aircraft    
       ,WorkOrder   
       ,EngOrder    
       ,Description 
       ,PartNo  
       ,InstPosition
from   (
        select  t.*
               ,max(rn) over(partition by WorkOrder, EngOrder, PartNO)               as max_rn
               ,max(rn) over(partition by WorkOrder, EngOrder, PartNO, INSTPOSITION) as max_rn2
        from   (
                select  t.*
                       ,row_number() over(partition by WorkOrder, EngOrder, PartNO order by WORKORDER) as rn
                       ,row_number() over(partition by WorkOrder, EngOrder, PartNO,INSTPOSITION order by WORKORDER)                as rn2
                from    t
               ) t
       ) t
where  max_rn > 1 
  and  max_rn2 = 1  

Fiddle