locked
Select data based on a column particular value RRS feed

  • Question

  • User445916681 posted

    HI,

    I have table  employee like below

    ID        NAME          CITY

    1          MAthew     Bangalore

    1           MAthew     Mysore

    2            INSAF         DELHI

    3            SONALI       MYSORE

    3            SONALI        DELHI

    Here the same 2 employee have two cities and one have only one.

    First priority is here for city name 'MYSORE'.If an employee have city 'MYSORE' then only that row should return.Else return the other City Name.

    So after the select statement the result should come like

    ID        NAME          CITY

    1           MAthew     Mysore

    2            INSAF         DELHI

    3            SONALI       MYSORE

    How can i select this?

    Thanks in advance.

    Wednesday, March 4, 2020 12:40 PM

All replies

  • User77042963 posted
    CREATE TABLE employee(
       ID   INTEGER  NOT NULL  
      ,NAME VARCHAR(6) NOT NULL
      ,CITY VARCHAR(9) NOT NULL
    );
    INSERT INTO employee(ID,NAME,CITY) VALUES
     (1,'MAthew','Bangalore')
    ,(1,'MAthew','Mysore')
    ,(2,'INSAF','DELHI')
    ,(3,'SONALI','MYSORE')
    ,(3,'SONALI','DELHI');
    
    
    ;with mycte as (
    select * 
    , row_number() over(partition by name order by Case when City='Mysore' then 1 else 2 end) rn
    from employee
    )
    
    select ID,Name,City
    from mycte
    where rn=1
    Order by ID
    
    drop table employee

    Wednesday, March 4, 2020 2:50 PM