none
Partition Syntax RRS feed

  • Question

  • I've spent more time on this than I am willing to admit and can't seem to figure it out the easy eloquent way of using partitions.  I would like to write into my select statement a partition code that returns an 'X' if the condition is true.

    Per my grid example below, I want to select the VEND_ID only if there is an abscense of 'COM1' in the ADDR_DC column. 

    VEND_ID      ADDR_DC
    ANSYS          COM1
    ANSYS          PMT1
    COMCAST    COM1
    CTCORP        COM1
    CTCORP         PMT1
    DACORP        PMT1
    DACORP        WIRE1
    EASYPOWER   WIRE1
    IRT                   PMT1
    IRT                   WIRE1
    KIRKLAND      PMT1

    So my preferred result regarding VEND_ID would be as follows

       ANSYS will not extract as there is at least one COM1 in the partition.  
       COMCAST (although only one entry) will not extract 
       CTCORP will not extract
       DACORP will extract as there is no COM1.  
       EASYPOWER (although only one entry) will extract as the ADDR_DC is not a COM1
       Both IRT and KIRKLAND will extract.

    For what it is worth, here are my attempts for a few of the filter columns...

    case
    When count(ADDR_DC) over (partition by VEND_ID) = 1 and ADDR_DC not in('COM1') then 'X' else Null
    end AS FILTER,

    case
    When ADDR_DC not in('COM1') then count(VEND_ID) over (partition by VEND_ID,ADDR_DC)else 0
    end AS FILTER

    My desired result:

    Filter   VEND_ID
    NULL    ANSYS
    NULL   COMCAST
    NULL   CTCORP
    X DACORP
    X EASYPOWER
    X IRT
    X KIRKLAND

    Any suggestion on the syntax would be appreciated.  
    Brian J


    • Edited by BrianJohn Monday, December 23, 2019 2:25 PM
    Monday, December 23, 2019 2:17 PM

Answers

  • CREATE TABLE mytable(
       VEND_ID VARCHAR(9) NOT NULL  
      ,ADDR_DC VARCHAR(5) NOT NULL
    );
    INSERT INTO mytable(VEND_ID,ADDR_DC) VALUES
     ('ANSYS','COM1')
    ,('ANSYS','PMT1')
    ,('COMCAST','COM1')
    ,('CTCORP','COM1')
    ,('CTCORP','PMT1')
    ,('DACORP','PMT1')
    ,('DACORP','WIRE1')
    ,('EASYPOWER','WIRE1')
    ,('IRT','PMT1')
    ,('IRT','WIRE1')
    ,('KIRKLAND','PMT1');
    
    
    ;with mycte as (Select *, sum(case when ADDR_DC='COM1' then 1 else 0 end) over(partition by  VEND_ID) cnt
    from  mytable
    )
    
    select VEND_ID,ADDR_DC
    from mycte 
    Where cnt=0
    
    drop TABLE mytable

    • Marked as answer by BrianJohn Monday, December 23, 2019 2:31 PM
    Monday, December 23, 2019 2:27 PM
    Moderator

All replies

  • CREATE TABLE mytable(
       VEND_ID VARCHAR(9) NOT NULL  
      ,ADDR_DC VARCHAR(5) NOT NULL
    );
    INSERT INTO mytable(VEND_ID,ADDR_DC) VALUES
     ('ANSYS','COM1')
    ,('ANSYS','PMT1')
    ,('COMCAST','COM1')
    ,('CTCORP','COM1')
    ,('CTCORP','PMT1')
    ,('DACORP','PMT1')
    ,('DACORP','WIRE1')
    ,('EASYPOWER','WIRE1')
    ,('IRT','PMT1')
    ,('IRT','WIRE1')
    ,('KIRKLAND','PMT1');
    
    
    ;with mycte as (Select *, sum(case when ADDR_DC='COM1' then 1 else 0 end) over(partition by  VEND_ID) cnt
    from  mytable
    )
    
    select VEND_ID,ADDR_DC
    from mycte 
    Where cnt=0
    
    drop TABLE mytable

    • Marked as answer by BrianJohn Monday, December 23, 2019 2:31 PM
    Monday, December 23, 2019 2:27 PM
    Moderator
  • Wow!  You did in seconds what I was struggling with for hours.  Thank you!
    Brian J
    Monday, December 23, 2019 2:32 PM
  • Using Not Exists should work as well:

    Select VEND_ID,ADDR_DC
    from  mytable t1
    where not exists(select 1 from mytable t2 where t2.VEND_ID=t1.VEND_ID and t2.ADDR_DC='COM1')
     


    Monday, December 23, 2019 5:26 PM
    Moderator