T-SQL Groups Query

Question

Hi - I have been breaking my head to get around a deciving problem that looks very simple.

Imagine a table like below

Desired Output

here i am grouping my records based on location.

in group 1 i.e. Eric if he is present in Denver and New York then I want only New records

group 2 i.e. Sam, since he is present in only 1 location I want that

Group 3 .i.e. Henry, since he is present in New york I want that record

here's the sql code to generate table

create table #EMP

(

EmpName varchar(20),

EmpLocation varchar(20)

)

insert #EMP (EmpName, EmpLocation) values ('Eric', 'Denver')

insert #EMP (EmpName, EmpLocation) values ('Eric', 'New York')

insert #EMP (EmpName, EmpLocation) values ('Sam', 'Denver')

insert #EMP (EmpName, EmpLocation) values ('Henry', 'New York')

Any help is appreciate, thanks

Answer 1

create table #EMP

(

EmpName varchar(20),

EmpLocation varchar(20)

)

insert #EMP (EmpName, EmpLocation) values ('Eric', 'Denver')

insert #EMP (EmpName, EmpLocation) values ('Eric', 'New York')

insert #EMP (EmpName, EmpLocation) values ('Sam', 'Denver')

insert #EMP (EmpName, EmpLocation) values ('Henry', 'New York')

;with mycte as (select * , row_number() over(partition by EmpName order by EmpLocation desc ) rn

from #EMP

)

Select EmpName, EmpLocation

from mycte

where rn=1

drop TABLE #EMP