Last post Mar 04, 2020 02:50 PM by limno
Mar 04, 2020 12:40 PM|insafkpm|LINK
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
How can i select this?
Thanks in advance.
Mar 04, 2020 02:50 PM|limno|LINK
CREATE TABLE employee(
ID INTEGER NOT NULL
,NAME VARCHAR(6) NOT NULL
,CITY VARCHAR(9) NOT NULL
INSERT INTO employee(ID,NAME,CITY) VALUES
;with mycte as (
, row_number() over(partition by name order by Case when City='Mysore' then 1 else 2 end) rn
Order by ID
drop table employee