locked
Get Distinct rows based on 2 columns values RRS feed

  • Question

  • User-1100884601 posted

    Hi 

    I have a table called tblPO and 3 fields described below . All 3 fields are  VARCHAR  data type

    PO_NO  ITEMNO STATUS
    1 10 x
    1 20 y
    2 30 x
    2 10 x
    2 20 x
    3 10 x
    3 20 x

    I want a query to return only  distinct PO_NO those all ITEMNO have same status  using where clause

    e.g "Where STATUS='x' "  then i should get  below result because only PO_NO 2 and 3 have all items x , I should not get PO_No 1 in result . Remember all fields datatype are VARCHAR

    PO_NO  STATUS
    2 x
    3 x

    Any solution will helpful me. 

    Friday, February 8, 2019 3:38 PM

Answers

  • User77042963 posted
    CREATE TABLE tblPO(
       PO_NO  VARCHAR(2)  NOT NULL  
      ,ITEMNO VARCHAR(2)  NOT NULL
      ,STATUS VARCHAR(1) NOT NULL
    );
    INSERT INTO tblPO(PO_NO,ITEMNO,STATUS) VALUES
     (1,10,'x')
    ,(1,20,'y')
    ,(2,30,'x')
    ,(2,10,'x')
    ,(2,20,'x')
    ,(3,10,'x')
    ,(3,20,'x');
    
    
    Select Distinct PO_NO, STATUS from tblPO m
    where STATUS='x' 
    and Not exists (select 1 from tblPO t where STATUS='y' and m.PO_NO=t.PO_NO)
    
    
    drop table  tblPO   

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 8, 2019 9:15 PM

All replies

  • User77042963 posted
    CREATE TABLE tblPO(
       PO_NO  VARCHAR(2)  NOT NULL  
      ,ITEMNO VARCHAR(2)  NOT NULL
      ,STATUS VARCHAR(1) NOT NULL
    );
    INSERT INTO tblPO(PO_NO,ITEMNO,STATUS) VALUES
     (1,10,'x')
    ,(1,20,'y')
    ,(2,30,'x')
    ,(2,10,'x')
    ,(2,20,'x')
    ,(3,10,'x')
    ,(3,20,'x');
    
    
    Select Distinct PO_NO, STATUS from tblPO m
    where STATUS='x' 
    and Not exists (select 1 from tblPO t where STATUS='y' and m.PO_NO=t.PO_NO)
    
    
    drop table  tblPO   

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 8, 2019 9:15 PM
  • User-1100884601 posted

    Hi  

    Thanks for  your help. 

    Regard 

    Udal

    Wednesday, February 13, 2019 11:13 AM